Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
I have a fairly large spread sheet that I usually filter or us a pivot table
to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
=SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
-- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... I have a fairly large spread sheet that I usually filter or us a pivot table to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
Thank you Bob, that worked great!! I have one more question, that I didn't
think of earlier. What if I wanted the criteria for column C to be 1 or 2 instead of just 1? Thank You Again, -- Daniel "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... I have a fairly large spread sheet that I usually filter or us a pivot table to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
Yeah, that is possible too
=SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(IS BLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... Thank you Bob, that worked great!! I have one more question, that I didn't think of earlier. What if I wanted the criteria for column C to be 1 or 2 instead of just 1? Thank You Again, -- Daniel "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... I have a fairly large spread sheet that I usually filter or us a pivot table to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
Thanks Again!! I don't think I would've gotten that one on my own!!
-- Daniel "Bob Phillips" wrote: Yeah, that is possible too =SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(IS BLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... Thank you Bob, that worked great!! I have one more question, that I didn't think of earlier. What if I wanted the criteria for column C to be 1 or 2 instead of just 1? Thank You Again, -- Daniel "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... I have a fairly large spread sheet that I usually filter or us a pivot table to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count nonblank cells with multiple criteria
No, it is not an intuitive leap from the previous one :-))
Bob "Daniel" wrote in message ... Thanks Again!! I don't think I would've gotten that one on my own!! -- Daniel "Bob Phillips" wrote: Yeah, that is possible too =SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(IS BLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... Thank you Bob, that worked great!! I have one more question, that I didn't think of earlier. What if I wanted the criteria for column C to be 1 or 2 instead of just 1? Thank You Again, -- Daniel "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20)))) -- HTH RP (remove nothere from the email address if mailing direct) "Daniel" wrote in message ... I have a fairly large spread sheet that I usually filter or us a pivot table to get what I need and then manually input into another workbook. What I need is to count the nonblank cells in column G (which consists of times in hh:mm:ss format) based on column A = KM06 and column C = 1 (both general format). I have tried using the sumproduct, sumif, if, and count. Thank You in advance, -- Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
I need to count while using multiple criteria | Excel Worksheet Functions | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) |