Adding a Third Criteria (Counting problem)

  • Thread starter Thread starter JimS
  • Start date Start date
J

JimS

I want to add a third criteria to the following formula:

=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5))

I want to do a total count of every instance in column L that is
greater than zero and that also matches the above data...

I tried this but it gave an incorrect answer:

=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5)*(L22:L5000>0))

If I were to creata an example it would look like the following. I
want to know how many times (column C) is greater than zero for every
day that = hs. Answer would be 2.

1/4/09 hs 9
1/5/09 hs 4
1/6/09 hs -5
1/7/09 ty 7
 
=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5)*(L22:L5000>0))

I'd expect that to work, Jim, unless column L contains text (including
formula blanks) that would be deemed to be greater than zero....and
that would lead to overcounting, possibly. Try adding another
criterion to check that L22:L5000 is numeric.

=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5)*(L22:L5000>0)
*ISNUMBER(L22:L5000))

If that doesn't work then perhaps all your "numbers" in L22:L5000 are
text formatted.....

Do you have formulas in L22:L5000, if so can you post an example here?
 
Hi,

Just to be on the safe side it might be better to use

=SUMPRODUCT(--($B$22:$B$5000>0),--($H$22:$H$5000=$H$5),--($L$22:$L$5000>0))

but if you want to use the other form, here is a shorter version

=SUMPRODUCT(($B$22:$B$5000*$L$22:$L$5000>0)*($H$22:$H$5000=$H$5))
 
I tried all of the formulas suggested and none of them came up with
the correct answer.

I have formulas in the entire L column that look like this:

=IF(J17="","",IF(J18="",SUM(J$15:J17)-SUM((L15:L$16))))
 
I tried all of the formulas suggested and none of them came up with
the correct answer.

I have formulas in the entire L column that look like this:

=IF(J17="","",IF(J18="",SUM(J$15:J17)-SUM((L15:L$16))))





- Show quoted text -
Click to expand...

Hello Jim,

As suggested your original formula didn't work because L22:L5000
contains some formula blanks which are deemed to be greater than zero
[in Excel and text value is deemed to be greater than any number]. My
suggestion above should cope with that problem, i.e. it only counts
L22:L5000 entries which are both numeric and > 0

=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5)*(L22:L5000>0)
*ISNUMBER(L22:L5000))

If that doesn't work then perhaps you have the same issue with column
B as with column L. Are there formulas in column B? Try changing to

=SUMPRODUCT(ISNUMBER($B$22:$B$5000)*($H$22:$H$5000=$H$5)*
(L22:L5000>0)
*ISNUMBER(L22:L5000))
 
Thanks for your help. I found my problem. Changing the L column was
necessary, thank you for that, but I simply eliminated the B column
from the formula, (turns out I didn't need it), and now I get the
correct answers.

I tried all of the formulas suggested and none of them came up with
the correct answer.

I have formulas in the entire L column that look like this:

=IF(J17="","",IF(J18="",SUM(J$15:J17)-SUM((L15:L$16))))





- Show quoted text -
Click to expand...

Hello Jim,

As suggested your original formula didn't work because L22:L5000
contains some formula blanks which are deemed to be greater than zero
[in Excel and text value is deemed to be greater than any number]. My
suggestion above should cope with that problem, i.e. it only counts
L22:L5000 entries which are both numeric and > 0

=SUMPRODUCT(($B$22:$B$5000>0)*($H$22:$H$5000=$H$5)*(L22:L5000>0)
*ISNUMBER(L22:L5000))

If that doesn't work then perhaps you have the same issue with column
B as with column L. Are there formulas in column B? Try changing to

=SUMPRODUCT(ISNUMBER($B$22:$B$5000)*($H$22:$H$5000=$H$5)*
(L22:L5000>0)
*ISNUMBER(L22:L5000))
Click to expand...
 
Back
Top