Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Debi
 
Posts: n/a
Default how do I count the numbers of row that meet 2 criteria

I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would like
the total number of rows that have the initials DR in column b and the letter
f in column j. Sound simple but I can't seem to get it
  #2   Report Post  
Duke Carey
 
Posts: n/a
Default how do I count the numbers of row that meet 2 criteria

=sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))

"Debi" wrote:

I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would like
the total number of rows that have the initials DR in column b and the letter
f in column j. Sound simple but I can't seem to get it

  #3   Report Post  
Debi
 
Posts: n/a
Default how do I count the numbers of row that meet 2 criteria

Thanks for your assistance.
Woud you be able to clarify one thing for me thougjh? What do the double
minus sign represent (--)

"Duke Carey" wrote:

=sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))

"Debi" wrote:

I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would like
the total number of rows that have the initials DR in column b and the letter
f in column j. Sound simple but I can't seem to get it

  #4   Report Post  
Biff
 
Posts: n/a
Default how do I count the numbers of row that meet 2 criteria

Hi!

Try this:

=SUMPRODUCT(--(B:B100="DR"),--(J1:J100="F"))

Better:

A1 = DR
A2 = F

=SUMPRODUCT(--(B:B100=A1),--(J1:J100=A2))

Biff

"Debi" wrote in message
...
I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would
like
the total number of rows that have the initials DR in column b and the
letter
f in column j. Sound simple but I can't seem to get it



  #5   Report Post  
Duke Carey
 
Posts: n/a
Default how do I count the numbers of row that meet 2 criteria

Debi -

The portion of the formula that reads (B1:B1000="DR") will return an array
of TRUE and FALSE values. The -- operator converts the Trues to 1 and the
Falses to 0. Sumproduct then multiplies each element in the array by the
corresponding element in the other array, and sums the products. The factors
are all 1s and 0s, so the only ones that yield a non-zero product are the
ones where both logical tests are TRUE.

"Debi" wrote:

Thanks for your assistance.
Woud you be able to clarify one thing for me thougjh? What do the double
minus sign represent (--)

"Duke Carey" wrote:

=sumproduct(--(B1:B1000="DR"),--(J1:J1000="f"))

"Debi" wrote:

I am atempting to count how many occurances there are when two criteria are
met in the same row but different cells For example in row b there are
initials and in row j there are two different letter f and h. I would like
the total number of rows that have the initials DR in column b and the letter
f in column j. Sound simple but I can't seem to get it

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
Sum Count of Two Numbers in the same Row Sam via OfficeKB.com Excel Worksheet Functions 8 September 18th 05 08:11 PM
count cells using multiple criteria Alex68 Excel Discussion (Misc queries) 4 May 24th 05 05:26 PM
Count using complex criteria Rob Excel Worksheet Functions 2 May 4th 05 02:34 PM
Count and Sum Total occurrances of two specific numbers Sam via OfficeKB.com Excel Worksheet Functions 10 March 29th 05 08:13 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM


All times are GMT +1. The time now is 10:38 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"