I want to count relying upon conditions in other cells

  • Thread starter Thread starter Looker1
  • Start date Start date
L

Looker1

I have 3 columns. X has the month in it, Y has type in it, and Z has a
numeric value.

I need to be able to show the count for the number of times a type occurs in
a given month if it has a numeric value other than 0. I have tried using
COUNTIF but it only returns 1 as the answer (which I know is wrong) Please
save my PC from certain destruction!
 
Try the following array formula:

=SUM(IF((X2:X1000="Oct")*(Y2:Y1000="Type A")*(Z2:Z1000<>0),1,0))

(you need to enter it as an array formula, that is, pressing
Ctrl+Shift+Enter instead of just Enter)
 
Many thanks Idoia!

Idoia said:
Try the following array formula:

=SUM(IF((X2:X1000="Oct")*(Y2:Y1000="Type A")*(Z2:Z1000<>0),1,0))

(you need to enter it as an array formula, that is, pressing
Ctrl+Shift+Enter instead of just Enter)
Click to expand...
 
no need to use * and --, that is built-in redundancy.

--
__________________________________
HTH

Bob

edvwvw via OfficeKB.com said:
If you do not want to use the array formula:

=SUMPRODUCT((--(X1:X1000="JUN"))*(--(Y1:Y1000="A"))*(--(Z1:Z1000<>0)))

edvwvw

Try the following array formula:

=SUM(IF((X2:X1000="Oct")*(Y2:Y1000="Type A")*(Z2:Z1000<>0),1,0))

(you need to enter it as an array formula, that is, pressing
Ctrl+Shift+Enter instead of just Enter)
I have 3 columns. X has the month in it, Y has type in it, and Z has a
numeric value.
Click to expand...
[quoted text clipped - 3 lines]
COUNTIF but it only returns 1 as the answer (which I know is wrong)
Please
save my PC from certain destruction!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top