I want to count relying upon conditions in other cells
 

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!

I want to count relying upon conditions in other cells
 

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)

"Looker1" wrote:

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!

I want to count relying upon conditions in other cells
 

Many thanks Idoia!

"Idoia" wrote:

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)

"Looker1" wrote:

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!

I want to count relying upon conditions in other cells
 

=SUMPRODUCT(--(X2:X20="Oct"),--(Y2:Y20="type"),--(Z2:Z20<0))


--
__________________________________
HTH

Bob

"Looker1" wrote in message
...
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!

I want to count relying upon conditions in other cells
 

If you do not want to use the array formula:

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

edvwvw


Idoia wrote:
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.
[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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1

I want to count relying upon conditions in other cells
 

no need to use * and --, that is built-in redundancy.

--
__________________________________
HTH

Bob

"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:85e793d4257d2@uwe...
If you do not want to use the array formula:

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

edvwvw


Idoia wrote:
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.
[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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200806/1