mulitple criteria
 

Problem:

I need to be able to do a multiple count if stmnt. Ex:

if column c = 690 and column d = green

thanks
D :confused:


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

Hi!

Try this:

=SUMPRODUCT(--(C1:C100=690),--(D1:D100="green"))

Better:

A1 = 690
B1 = green

=SUMPRODUCT(--(C1:C100=A1),--(D1:D100=B1))

Biff

"imjustme" wrote in
message ...

Problem:

I need to be able to do a multiple count if stmnt. Ex:

if column c = 690 and column d = green

thanks
D :confused:


--
imjustme
------------------------------------------------------------------------
imjustme's Profile:
http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

imjustme Wrote:
Problem:

I need to be able to do a multiple count if stmnt. Ex:

if column c = 690 and column d = green

thanks
D :confused:

Try this formula

=SUMPRODUCT(--($C$1:$C$100=690),--($D$1:$D$100=\"GREEN\"))

and see if it will help you.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=400959

Try...

=SUMPRODUCT(--($C$1:$C$100=690),--($D$1:$D$100="Green"))

or

=SUMPRODUCT(--($C$1:$C$100=G1),--($D$1:$D$100=H1))

....where G1 contains your first criterion, such as 690, and H1 contains
your second criterion, such as Green. Adjust the range accordingly.

Hope this helps!

In article ,
imjustme
wrote:

Problem:

I need to be able to do a multiple count if stmnt. Ex:

if column c = 690 and column d = green

thanks
D :confused:

I just want to thank everyone that responded to my question... the
formula is great.. except that it adds five to my final answer. I can
not figure out why. I have even manually counted to make sure.. and it
is adding 5 more. Any clue why?? my range is from 1-1000.

thanks
dawn-tx


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

I think the formula is right, but your count is incorrect.

You could add another column (say E, just for double checking) and put a formula
like:

=if(and(c1=690,d1="green"),1,0)

And drag down all 1000 rows.

Then use =sum(e1:e1000)

to verify.

imjustme wrote:

I just want to thank everyone that responded to my question... the
formula is great.. except that it adds five to my final answer. I can
not figure out why. I have even manually counted to make sure.. and it
is adding 5 more. Any clue why?? my range is from 1-1000.

thanks
dawn-tx

--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

--

Dave Peterson

Dave:

I did figure out what the problem was.. and it was not with the
formula.. I LOVE THE FORMULA...

I need it to count only if it is true in both columns.. not just one.

That was my problem


thanks
dawn-tx


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

I would use an array formula. You can have as many criteria conditions
as you want, but the last range (in this case e1:e100) has to be full
of numbers. Then you click into the formula to edit it and hold
control and shift and press enter to make it an array formula. The
formula will look for cells in range c1:c100 that have "690" then look
for those cells in range d1:d100 that have "green" and then count the
number of occurances in e1:e100. (If your spreadsheet already has a
column of numbers, such as a count, then use that instead of e1:e100).
Important that all the ranges are the same.

=count(if(c1:c100="690",if(d1:d100,"green",e1:e100 )))

After you click into the cell and hold shift and control and press
enter the formula will be:

{=count(if(c1:c100="690",if(d1:d100,"green",e1:e10 0)))}


--
belly0fdesire
------------------------------------------------------------------------
belly0fdesire's Profile: http://www.excelforum.com/member.php...o&userid=25974
View this thread: http://www.excelforum.com/showthread...hreadid=400959

This is my formula:

=SUMPRODUCT(--(sept!$C$1:$C$1000=690),--(sept!$H$1:$H$1000="approved"--(sept!$F$1:$F$1000="consult")))

and as it stands.. it reads as "OR" I need it to read "AND" in
between each instance... so it will pick up when one row meets all of
the criteria.

thanks
dawn-tx


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=400959

Try...

=SUMPRODUCT(--(sept!$C$1:$C$1000=690),--(sept!$H$1:$H$1000="approved"),--
(sept!$F$1:$F$1000="consult"))

Hope this helps!

In article ,
imjustme
wrote:

This is my formula:

=SUMPRODUCT(--(sept!$C$1:$C$1000=690),--(sept!$H$1:$H$1000="approved"--(sept!$
F$1:$F$1000="consult")))

and as it stands.. it reads as "OR" I need it to read "AND" in
between each instance... so it will pick up when one row meets all of
the criteria.

thanks
dawn-tx