CODEWITHSUNDEEP
excelexcel-formula
VagrantC
VagrantC

Reputation: 827

Counting rows with criteria and not counting the same row more than once

So I'm looking to count how many rows contain the value "X" in at least one of 5 columns, and then exclude any of those rows that contain the same value in 2 different columns, but I'm not sure that my function is doing what I think it is.

So basically this: "Get all rows that contain "X" in AT LEAST one of column A, B, C, D, E, then exclude any of those rows where value in column F is equal to value in column G"

This is what I have, but I don't think it's right and the number I get seems very low:

=SUMPRODUCT((A1:A100="X")*(B1:B100="X")*(C1:C100="X")*(D1:D100="X")*(E1:E100="X")*(F1:F100<>G1:G100))

Upvotes: 0

Views: 81

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

In H1 enter:

=IF(AND(COUNTIF(A1:E1,"X")>0,F1<>G1),1,0)

and copy down to H100

Then all you need is:

=SUM(H1:H100)

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14754

This will do it:

=SUMPRODUCT((0<(A1:A100="x")+(B1:B100="x")+(C1:C100="x")+(D1:D100="x")+(E1:E100="x"))*(F1:F100<>G1:G100))

There is no need to use any helper cells for this simple calculation.

Upvotes: 1

trincot
trincot

Reputation: 349946

For such combined conditions, it is better to reserve a few columns for partial results (which you could hide later). This is much easier to progressively make, while being able to check the partial results are correct.

So put a formula in H1 that counts the number of Xs in A1:E1

=COUNTIF(A1:E1; "X")

Copy that formula further down the H column. Then in I1, put a test on F1<>G1

=IF(F1<>G1; 1; 0)

Copy that formula down the I column. Then in J1 see if the row should be counted based on these two partial results:

=IF(H1*I1=0; 0; 1)

Copy that formula down the J column. Now your final result is

=SUM(J1:J10000)

Of course, you can do all that in one formula, but that is much harder to debug. In the above way, you can easily spot errors in the intermediate results. And once you are happy with the result, you just hide the intermediate calculation columns.

Upvotes: 0

Related Questions