# Count nonblank cells with multiple criteria

1. ## 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. ## Re: 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" <Daniel@discussions.microsoft.com> wrote in message
news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
> 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. ## Re: 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" <Daniel@discussions.microsoft.com> wrote in message
> news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
> > 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. ## Re: Count nonblank cells with multiple criteria

Yeah, that is possible too

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

--

HTH

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

"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
> 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" <Daniel@discussions.microsoft.com> wrote in message
> > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
> > > 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. ## Re: 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(ISBLANK(G2:G20))))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Daniel" <Daniel@discussions.microsoft.com> wrote in message
> news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
> > 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" <Daniel@discussions.microsoft.com> wrote in message
> > > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
> > > > 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. ## Re: Count nonblank cells with multiple criteria

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

Bob

"Daniel" <Daniel@discussions.microsoft.com> wrote in message
news:E679C647-2AC6-4EF3-9B80-40005B55876D@microsoft.com...
> 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(ISBLANK(G2:G20))))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Daniel" <Daniel@discussions.microsoft.com> wrote in message
> > news:C3C428AF-618B-4CAB-AFCC-31B052746364@microsoft.com...
> > > 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" <Daniel@discussions.microsoft.com> wrote in message
> > > > news:4C24688A-A5FE-4915-B8DB-C40F22D6DF1B@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >

> >
> >
> >

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1