Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Daniel
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Daniel
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Daniel
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display first, second, etc Nonblank Cells in a Range Jeremy N. Excel Worksheet Functions 12 September 25th 05 01:47 PM
I need to count while using multiple criteria Larry Excel Worksheet Functions 1 July 21st 05 04:56 PM
Multiple Criteria IF Nesting BethB Excel Worksheet Functions 2 May 17th 05 12:14 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 11:54 AM
Counting rows based on criteria in multiple cells Margaret Excel Discussion (Misc queries) 11 December 3rd 04 12:04 AM


All times are GMT +1. The time now is 10:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"