Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
Not sure I understood the part about the F column but try this:
=SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy" )*OR(F1:F10="yyy")*(G1:G10<"zzz")) HTH JG "telewats" wrote: I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
=SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,y yy",F17:F20="yyy"))*(G17:G20<"zzz"))
seems to work for me -- Greetings from New Zealand Bill K "pinmaster" wrote in message ... Not sure I understood the part about the F column but try this: =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy" )*OR(F1:F10="yyy")*(G1:G10<"zzz")) HTH JG "telewats" wrote: I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
"Bill Kuunders" wrote in message
... =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,y yy",F17:F20="yyy"))*(G17:G20<"zzz")) seems to work for me If it works for you then that is fine but it doesn't work for me. I don't think that OR works in a SUMPRODUCT function. When I enter xxx in B17:B20, zzz in G18 & G20 and just z in G19 then if I put yyy in ANY cell in F17:F20 the formula return 2. In other words the OR is making the yyy apply to all cells in F17:F20 The "+" in the formula I posted works like an OR but I see that I forgot to eliminate the zzz in column G so it should have been: SUMPRODUCT((B1:B30="xxx")*((F1:F30="yyy1")+(F1:F30 ="yyy2")+(F1:F30="yyy3"))*(G1:G30<"zzz")) -- HTH Sandy with @tiscali.co.uk "Bill Kuunders" wrote in message ... =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,y yy",F17:F20="yyy"))*(G17:G20<"zzz")) seems to work for me -- Greetings from New Zealand Bill K "pinmaster" wrote in message ... Not sure I understood the part about the F column but try this: =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy" )*OR(F1:F10="yyy")*(G1:G10<"zzz")) HTH JG "telewats" wrote: I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
I was going to reply saying that my formula didn't work after all but I guess
it should be obvious by now. Glad someone was able to come up with something that works. Learned something new in the process so that's good, thanks Sandy. Regards JG "Sandy Mann" wrote: Try: =SUMPRODUCT((B1:B30="xxx")*((F1:F30="yyy1")+(F1:F3 0="yyy2")+(F1:F30="yyy3"))*(G1:G30="zzz")) Although I'm sure that there are more efficient way of doing it. -- HTH Sandy with @tiscali.co.uk "telewats" wrote in message ... I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
"pinmaster" wrote in message
... I was going to reply saying that my formula didn't work after all but I guess it should be obvious by now. Glad someone was able to come up with something that works. I'm still waiting for Aladin Akyurek to happen by and say that it is inefficient <g -- Regards Sandy with @tiscali.co.uk |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
Inefficient?? I don't know about that, seems an obvious solution to me!
Regards JG "Sandy Mann" wrote: "pinmaster" wrote in message ... I was going to reply saying that my formula didn't work after all but I guess it should be obvious by now. Glad someone was able to come up with something that works. I'm still waiting for Aladin Akyurek to happen by and say that it is inefficient <g -- Regards Sandy with @tiscali.co.uk |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula blues....
Thank You
I didn't check it all because I wasn't expecting any problems. again I'm learning every day............ Bill K "Sandy Mann" wrote in message ... "Bill Kuunders" wrote in message ... =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,y yy",F17:F20="yyy"))*(G17:G20<"zzz")) seems to work for me If it works for you then that is fine but it doesn't work for me. I don't think that OR works in a SUMPRODUCT function. When I enter xxx in B17:B20, zzz in G18 & G20 and just z in G19 then if I put yyy in ANY cell in F17:F20 the formula return 2. In other words the OR is making the yyy apply to all cells in F17:F20 The "+" in the formula I posted works like an OR but I see that I forgot to eliminate the zzz in column G so it should have been: SUMPRODUCT((B1:B30="xxx")*((F1:F30="yyy1")+(F1:F30 ="yyy2")+(F1:F30="yyy3"))*(G1:G30<"zzz")) -- HTH Sandy with @tiscali.co.uk "Bill Kuunders" wrote in message ... =SUMPRODUCT((B17:B20="xxx")*(OR(F17:F20="yyy,yyy,y yy",F17:F20="yyy"))*(G17:G20<"zzz")) seems to work for me -- Greetings from New Zealand Bill K "pinmaster" wrote in message ... Not sure I understood the part about the F column but try this: =SUMPRODUCT((B1:B10="xxx")*OR(F1:F10="yyy,yyy,yyy" )*OR(F1:F10="yyy")*(G1:G10<"zzz")) HTH JG "telewats" wrote: I am trying to determine the total count of cells containing certain data, and I can't get my formula to work correctly. I need to get the total number of cells that fit the following criteria: If column B contains xxx, and column F is equal to yyy, yyy, yyy, or yyy, and column G is not equal to zzz, then count the cells. I'm sure this can be done (right?) but I am not having any luck. -- telewats ------------------------------------------------------------------------ telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270 View this thread: http://www.excelforum.com/showthread...hreadid=503018 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |