Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 -- imjustme ------------------------------------------------------------------------ imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854 View this thread: http://www.excelforum.com/showthread...hreadid=400959 |
#2
|
|||
|
|||
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 -- imjustme ------------------------------------------------------------------------ imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854 View this thread: http://www.excelforum.com/showthread...hreadid=400959 |
#3
|
|||
|
|||
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 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions |