mulitple criteria

I

imjustme

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:
 
B

Biff

Hi!

Try this:

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

Better:

A1 = 690
B1 = green

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

Biff
 
B

BenjieLop

imjustme said:
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:
Click to expand...

Try this formula

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

and see if it will help you.

Regards.
 
D

Domenic

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!
 
I

imjustme

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
 
D

Dave Peterson

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.
 
I

imjustme

Dave:

I did figure out what the problem was.. and it was not with th
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-t
 
B

belly0fdesire

I would use an array formula. You can have as many criteria condition
as you want, but the last range (in this case e1:e100) has to be ful
of numbers. Then you click into the formula to edit it and hol
control and shift and press enter to make it an array formula. Th
formula will look for cells in range c1:c100 that have "690" then loo
for those cells in range d1:d100 that have "green" and then count th
number of occurances in e1:e100. (If your spreadsheet already has
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 pres
enter the formula will be:

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

imjustme

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" i
between each instance... so it will pick up when one row meets all o
the criteria.

thanks
dawn-t
 
D

Domenic

Try...

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

Hope this helps!
 
Top