Count cells based upon criteria in other cells

[JT]

The cells in column J contains donations made by various individuals. Cells
in column R contain certain member classifications. Am looking for a formula
that looks at column R, then counts the number of entries in the
corresponding cells in colum J for each member classsification. EX: in
column R there are three classifications: RB1, RB2, RB3. Need to count how
many donations in column J are made by each classification.

Many thanks!

 

[Jacob Skaria]

Try the below formula

=COUNTIF(J:J,"RB1")

 

[JT]

Jacob:

Thanks, however this formula does not find the criterial thatis in the R
column. Thye J columb contains donations...the R column contains the
criteria that I need the formula to consider as it counts the corresponding J
column cells.

 

[Pete_UK]

Do it this way, then:

=COUNTIF(R:R,"RB1")

and if you want to total the contributions which meet that criteria:

=SUMIF(R:R,"RB1",J:J)

Hope this helps.

Pete

 

[Jacob Skaria]

Do you mean.

=COUNTIF(R:R,"RB1")

--
Jacob

Jacob:

Thanks, however this formula does not find the criterial thatis in the R
column. Thye J columb contains donations...the R column contains the
criteria that I need the formula to consider as it counts the corresponding J
column cells.

 

[JT]

The SUMIF function works great, but I really need to know the number of cells
in J that correcpond to the number of RB1 criteria in column R; For example:
if there are 12 "RB1" designations out of 120 in R that have posted
contributions in the J column, I need the formula to return "12". If there
is no contribution listed in J for an RB1 designation in R, the formula
chould NOT count that J cell.

Does that help clarify what I'm looking for?

 

[Pete_UK]

Ah, right !! You didn't make that clear earlier - you have two
conditions to check for, rather than just one. COUNTIF (and SUMIF) can
only be used if you have one condition, so try this instead:

=SUMPRODUCT((R$1:R$100="RB1")*(J$1:J$100<>""))

This checks for column R containing RB1 AND J is not empty, and counts
the number that meet both criteria.

NOTE that you can't use full-column references with this function in
XL before version 2007, but adjust the ranges to suit your data (I've
assumed 100 rows).

Hope this helps.

Pete

 

[JT]

Pete: Works perfectly. Many thanks!

JT

Ah, right !! You didn't make that clear earlier - you have two
conditions to check for, rather than just one. COUNTIF (and SUMIF) can
only be used if you have one condition, so try this instead:

=SUMPRODUCT((R$1:R$100="RB1")*(J$1:J$100<>""))

This checks for column R containing RB1 AND J is not empty, and counts
the number that meet both criteria.

NOTE that you can't use full-column references with this function in
XL before version 2007, but adjust the ranges to suit your data (I've
assumed 100 rows).

Hope this helps.

Pete



.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Pete:  Works perfectly.  Many thanks!

JT








- Show quoted text -

 

[JT]

I have one more request.

I need a formula that looks at columns I and J. Column I contains
donations from 2009...J contains projected donations. Where there are
donations in cells in I, but no donation in adjacent cells in J, I'd like the
total from J. Example: in I5 there is a $50 donation, but no projected
donation in J5. The formula would return "1". Need the formula to look at
the range listed.

Thanks. thie will be the last one I post!

 

[Pete_UK]

Well, I think your conditions are column I not blank and column J
blank, so try this:

=SUMPRODUCT((i1:i100<>"")*(J1:J100=""))

Hope this helps.

Pete

 

[JT]

Pete: Something in ther formula isn't returning the correct value. In the
example below, the result of the formula SHOULD be "2". In the first two
rows, there are enteries in BOTH cells and shuotl NOT be counted...in two
others, there are entries in the 2009 column, but NOT in 2010. This is the
sort of comparison that should result in the value. The "0" entries in the
2009 column are meaningless.

Maybe this helps clarify what I am looking for.

2009 2010

3,800 3,900
1,560 1,560
375
0
0
0
900

 

[David Biddulph]

Well, now that you've thrown in the extra criterion about the zeroes, why
not change your formula to
=SUMPRODUCT((I1:I100<>"")*(I1:I100<>0)*(J1:J100="")) ?

 

[Pete_UK]

Yes, but those "0" entries will be counted as non-blank, giving you an
answer of 5 is this case instead of 2. Perhaps it would be better as:

=SUMPRODUCT((i1:i100>0)*(J1:J100=""))

Hope this helps.

Pete

 

[JT]

I'm sorry, David. Accept my apology. The formula works just fine. Thank you.

JT