Count nonblank cells with multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel - 17 Nov 2005 20:00 GMT

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,

Signature

Daniel

Reply to this Message

Bob Phillips - 17 Nov 2005 20:09 GMT

=SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> 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
[quoted text clipped - 5 lines]
>
> Thank You in advance,

Reply to this Message

Daniel - 17 Nov 2005 21:11 GMT

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,

Signature

Daniel

> =SUMPRODUCT(--(A2:A20="KM06"),--(C2:C20=1),--(NOT(ISBLANK(G2:G20))))
>
[quoted text clipped - 9 lines]
> >
> > Thank You in advance,

Reply to this Message

Bob Phillips - 17 Nov 2005 21:31 GMT

Yeah, that is possible too

=SUMPRODUCT((A2:A20="KM06")*(C2:C20={1,2})*(NOT(ISBLANK(G2:G20))))

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> 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
[quoted text clipped - 15 lines]
> > >
> > > Thank You in advance,

Reply to this Message

Daniel - 17 Nov 2005 21:51 GMT

Thanks Again!! I don't think I would've gotten that one on my own!!

Signature

Daniel

> Yeah, that is possible too
>
[quoted text clipped - 23 lines]
> > > >
> > > > Thank You in advance,

Reply to this Message

Bob Phillips - 17 Nov 2005 21:59 GMT

No, it is not an intuitive leap from the previous one :-))

Bob

> Thanks Again!! I don't think I would've gotten that one on my own!!
>
[quoted text clipped - 25 lines]
> > > > >
> > > > > Thank You in advance,

Reply to this Message