< Go Back

Count if row meets multiple internal criteria

You can easily count if row meets multiple internal criteria while working in excel spreadsheet. The counting of rows in the table having multiple criteria depends on logical assessment. In order to do so, SUMPRODUCT function can be used.

Formula

The formula to count if row meets multiple internal criteria in a table is given below

=SUMPRODUCT((logical1)*(logical2))

Explanation

SUMPRODUCT does not require Control + Shift+ Enter and is pre-set to deal with the arrangements natively. The default settings of SUMPRODUCT involve the multiplication of matching items in multiple arrays thereby leading to adding together products. In a single arrangement, it provides the sum of items in that array. In the example given below, we are utilizing two logical terms in a single arrangement. We could put every articulation into a different argument, however, then we’d have to constrain legitimate TRUE FALSE qualities to 1’s and 0’s with another operator. Excel will consequently force logical values to 1’s and 0’s by multiplying two arrays through multiplication operator.

Example

In the example given below, we have a table containing the result of a cricket match (See screenshot). There are four columns that represent the Home, Visitors, Home Score, and Visitors Score. We want to count only wins of the Home team so we used SUMPRODUCT function to count if row meets multiple internal criteria. In the example shown, H5 contains the formula

=SUMPRODUCT((B5:B10=G5)*(D5:D10>E5:E10))

Figure 1. Example of count if row meets multiple internal criteria in Excel

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar