CODEWITHSUNDEEP
excelexcel-formula
VagrantC
VagrantC

Reputation: 827

Function to count all rows with multiple criteria

I'm trying to use the COUNTIFS statement to count all rows where values in 4 different columns equal "something", while excluding rows where the values in two columns are equal. This is what I have for counting the rows where the 4 columns equal "something" but I can't figure out how to add the last part:

=COUNTIFS(A2:A100,"something",B2:B100,"something",C2:C100,"something",D2:D100,"something", [...])

Now I need to add another statement within this COUNTIFS at the [...] that says something like "exclude all rows where value in J is equal to value in K", but I can't seem to figure out how to do that WITHIN the COUNTIFS statement.

Upvotes: 0

Views: 76

Answers (2)

Som Shekhar
Som Shekhar

Reputation: 138

You could use an Array function for this.

={sum((A1:A1000 = 'Something')*(BB:B1000 = 'Something')*(C1:C1000 = 'Something')*(D1:D1000 = 'Something')*(J1:J1000 = K1=K1000))}

for entering an array function, you need to use Ctrl+Shift+Enter

More info at Excel Array Functions

Upvotes: 0

user4039065
user4039065

Reputation:

You likely have to move to a SUMPRODUCT function.

=SUMPRODUCT((A2:A100="something")*(B2:B100="something")*(C2:C100="something")*(D2:D100="something")*(J2:J100<>K2:K100))

Avoid full column references in SUMPRODUCT due to the cyclic nature of the calculation.

Upvotes: 1

Related Questions