Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
May I have help with this formula, please. I need to count how many times
"WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
Try something like
=SUMPRODUCT((F2:F30={"WW","SS"})*(ISNUMBER(O2:O30) )) note that unless you have 2007 you would need to specify the range you are using, also in your example you used F:F and not A:A so I used F as well whereas you wrote that you were testing column A -- Regards, Peo Sjoblom "Bonnie" wrote in message ... May I have help with this formula, please. I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
Thank you. But, there are some funny brackets arount the "WW","SS";
is this an array formula? When I typed it in I held down the CTRL and ALT but I didn't get the brackets as shown in the formula you wrote out. Please advise. Bonnie On Nov 3, 3:52*pm, "Peo Sjoblom" wrote: Try something like =SUMPRODUCT((F2:F30={"WW","SS"})*(ISNUMBER(O2:O30) )) note that unless you have 2007 you would need to specify the range you are using, also in your example you used F:F and not A:A so I used F as well whereas you wrote that you were testing column A -- Regards, Peo Sjoblom "Bonnie" wrote in message ... May I have help with this formula, please. *I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: *=COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS" ) *... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. *Bonnie- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
No the brackets are entered physically, no array formula entering and given
your conditions the formula works without taking a whole lot of space -- Regards, Peo Sjoblom "Bonnie" wrote in message ... Thank you. But, there are some funny brackets arount the "WW","SS"; is this an array formula? When I typed it in I held down the CTRL and ALT but I didn't get the brackets as shown in the formula you wrote out. Please advise. Bonnie On Nov 3, 3:52 pm, "Peo Sjoblom" wrote: Try something like =SUMPRODUCT((F2:F30={"WW","SS"})*(ISNUMBER(O2:O30) )) note that unless you have 2007 you would need to specify the range you are using, also in your example you used F:F and not A:A so I used F as well whereas you wrote that you were testing column A -- Regards, Peo Sjoblom "Bonnie" wrote in message ... May I have help with this formula, please. I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
Bonnie,
Try this but because you are using full columns it will be slow. I would recommend you shorten the range to something more practical. =SUMPRODUCT((Master!F:F="WW")*(ISNUMBER(Master!O:O )))+SUMPRODUCT((Master!F:F="SS")*(ISNUMBER(Master! O:O))) with shortened ranges =SUMPRODUCT((Master!F1:F25="WW")*(ISNUMBER(Master! O1:O25)))+SUMPRODUCT((Master!F1:F25="SS")*(ISNUMBE R(Master!O1:O25))) Another point to note is there is no ISDATE worksheet function in Excel so this looks for a number on col O Mike "Bonnie" wrote: May I have help with this formula, please. I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
On Nov 3, 4:25*pm, Mike H wrote:
Bonnie, Try this but because you are using full columns it will be slow. I would recommend you shorten the range to something more practical. =SUMPRODUCT((Master!F:F="WW")*(ISNUMBER(Master!O:O )))+SUMPRODUCT((Master!F:*F="SS")*(ISNUMBER(Master !O:O))) with shortened ranges =SUMPRODUCT((Master!F1:F25="WW")*(ISNUMBER(Master! O1:O25)))+SUMPRODUCT((Mas*ter!F1:F25="SS")*(ISNUMB ER(Master!O1:O25))) Another point to note is there is no ISDATE worksheet function in Excel so this looks for a number on col O Mike "Bonnie" wrote: May I have help with this formula, please. *I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: *=COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS" ) *... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. *Bonnie- Hide quoted text - - Show quoted text - This is apparently the formula to use. Three people suggestioned the same or similar type formulas. Thank you very much for your reply. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
Hi,
Try this =sumproduct((isnumber(match(F2:F50,F55:F56,0)))*is number(O2:O50)) F55:F56 contains WW and SS -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bonnie" wrote in message ... May I have help with this formula, please. I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Function Formula Question
Try
=SUMPRODUCT((ISNUMBER(MATCH(F1:F100,{"WW","SS"},0) ))* (ISNUMBER(O1:O100))) If this post helps click Yes --------------- Jacob Skaria "Bonnie" wrote: May I have help with this formula, please. I need to count how many times "WW" or"SS" are entered in Column F ... AND ... there is a date entered in Column O on the same Row as the WW or SS appear in Column A. I got this far: =COUNTIF(Master!F:F,"WW")+COUNTIF(Master!F:F,"SS") ... but I don't know how to include the reference to Column O. By the way, yes, the formula I'm creating is on a separate worksheet than the reference cells. Thank you, in advance, for any guidance you can give. Bonnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif function question | Excel Worksheet Functions | |||
Question on the countif function and times | Excel Worksheet Functions | |||
countif function question | Excel Worksheet Functions | |||
CountIf Function Question | Excel Discussion (Misc queries) | |||
Countif/Sumif function question | Excel Worksheet Functions |