multiple conditions counting

Hi,
Humbly requesting assistance. I am not able to derive the function that I need to count the total number of cases where both conditions are true.

I am trying to count the number cells that meet two conditions.
basically if the value "1" exists in column K, and the corresponding cell in column F also contains the value then count it.

Not sure if this is a COUNTIFS() function or a SUMPRODUCT() function.
Have tried multiple, but no glory as of yet.

Here's what I have: =SUMPRODUCT((K5:K221="1")*(F5:F221="1"))

I'm getting zero with that function. What I expected is that when column K5 is true and column F5 is true the count is incremented by 1. And it would count through row 221.

Thanks in advance!
Tony

In XL2007+ you can use COUNTIFS, in all versions you can use SUMPRODUCT

The problem is probably that you have the 1 in quotes...

should be

=COUNTIFS(K5:K221,1,F5:F221,1)

or

=SUMPRODUCT((K5:K221=1)*(F5:F221=1))

Originally Posted by NBVC

In XL2007+ you can use COUNTIFS, in all versions you can use SUMPRODUCT

The problem is probably that you have the 1 in quotes...

should be

=COUNTIFS(K5:K221,1,F5:F221,1)

or

=SUMPRODUCT((K5:K221=1)*(F5:F221=1))

Thanks SO MUCH for the solution!

The Sum product Function went straight to work! The countifs continued to return zero.

I've copied the function across cells, changing my ranges for new columns and its working out well.

applying the incremental function to the results array (10columns X 5rows.) is a bit awkward.

Dragging the formula across the row of 10 columns for my count does not increment the column change correctly.

more explanation: applying the solution formula to the first cell:
=SUMPRODUCT((K5:K221=-1)*(F5:F221=1))
Dragging it to the second cell in the row I only want K5:K221 to increment to L5:L221

Then when I drag the formula down, I want F5:F221 value to increment to G5:G221 and K range should remain the same.

Any Ideas? This is low priority, as I have already begun manually making the edit to the function for this case.

I see you have K5:K221 = -1

did you change the COUNTIFS too?

Also, can you show exactly what the 4 cells (2 rows * 2 columns) are supposed to look like.

e.g. if you have the original in A1, what does B1, A2 and B2 look like?

Originally Posted by NBVC

I see you have K5:K221 = -1

did you change the COUNTIFS too?

Also, can you show exactly what the 4 cells (2 rows * 2 columns) are supposed to look like.

e.g. if you have the original in A1, what does B1, A2 and B2 look like?

Ha! I did screw that up.. I just checked, making my value -1 does work in the COUNTIFS function...

Here is the first value:
=SUMPRODUCT((K5:K221=-1)*(F5:F221=1))
Dragging to the right across the grid should change to:
=SUMPRODUCT((L5:L221=-1)*(F5:F221=1)) <-- note that K increments to L then m, then n, etc. (K increments up to T)

I also drag down five rows.. using the same starting point:
=SUMPRODUCT((K5:K221=-1)*(F5:F221=1))
Next value is:
=SUMPRODUCT((K5:K221=1)*(G5:G221=1)) <-- F increments to G, and will increment all the way to J.

Issue in dragging is that I get both the Column Incrementation AND the cell value incrementation.


Here is my spreadsheet... (it's pretty fugly :S please excuse the mess)

I am not sure if what you currently have in the table is supposed to be correct or not...but there are inconsistencies based on your descriptions above.

First, only the top row has a condition to check for -1 (negative), then remaining rows show 1 (positive).

In some sections of the table, both ranges in the formula change in both directions... To me every column in each row should have the same first range as the first column, and every row in each column should have the same second range as the first row... as per my understanding of your description.

See attached.

In V54:

Please Login or Register  to view this content.

copied across row 54.

then in V55:

Please Login or Register  to view this content.

copied down and across the rest of the matrix.

Note: These can be adapted to COUNTIFS formulas too.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.