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!

Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob


"JT" wrote:

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:

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.

"Jacob Skaria" wrote:

Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob


"JT" wrote:

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!

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

On Dec 20, 2:26*pm, JT wrote:
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.



"Jacob Skaria" wrote:
Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob

"JT" wrote:

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!- Hide quoted text -

- Show quoted text -

Do you mean.

=COUNTIF(R:R,"RB1")

--
Jacob


"JT" wrote:

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.

"Jacob Skaria" wrote:

Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob


"JT" wrote:

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!

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" wrote:

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

On Dec 20, 2:26 pm, JT wrote:
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.



"Jacob Skaria" wrote:
Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob

"JT" wrote:

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!- Hide quoted text -

- Show quoted text -

.

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

On Dec 20, 4:10*pm, JT wrote:
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" wrote:
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

On Dec 20, 2:26 pm, JT wrote:
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.

"Jacob Skaria" wrote:
Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob

"JT" wrote:

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!- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

Pete: Works perfectly. Many thanks!

JT

"Pete_UK" wrote:

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

On Dec 20, 4:10 pm, JT wrote:
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" wrote:
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

On Dec 20, 2:26 pm, JT wrote:
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.

"Jacob Skaria" wrote:
Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob

"JT" wrote:

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!- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

.

You're welcome - thanks for feeding back.

Pete

On Dec 20, 6:26*pm, JT wrote:
Pete: *Works perfectly. *Many thanks!

JT



"Pete_UK" wrote:
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

On Dec 20, 4:10 pm, JT wrote:
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" wrote:
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

On Dec 20, 2:26 pm, JT wrote:
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.

"Jacob Skaria" wrote:
Try the below formula

=COUNTIF(J:J,"RB1")

--
Jacob

"JT" wrote:

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!- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

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!

"JT" wrote:

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!

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

On Dec 20, 7:09*pm, JT wrote:
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!



"JT" wrote:
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!- Hide quoted text -

- Show quoted text -

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


"Pete_UK" wrote:

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

On Dec 20, 7:09 pm, JT wrote:
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!



"JT" wrote:
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!- Hide quoted text -

- Show quoted text -

.

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="" )) ?
--
David Biddulph

"JT" wrote in message
...
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


"Pete_UK" wrote:

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

On Dec 20, 7:09 pm, JT wrote:
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!



"JT" wrote:
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!- Hide quoted text -

- Show quoted text -

.

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:i1000)*(J1:J100=""))

Hope this helps.

Pete

On Dec 20, 8:18*pm, JT wrote:
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 * *



"Pete_UK" wrote:
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

On Dec 20, 7:09 pm, JT wrote:
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!

"JT" wrote:
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!- Hide quoted text -

- Show quoted text -

.- Hide quoted text -

- Show quoted text -

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

JT

"David Biddulph" wrote:

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="" )) ?
--
David Biddulph

"JT" wrote in message
...
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


"Pete_UK" wrote:

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

On Dec 20, 7:09 pm, JT wrote:
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!



"JT" wrote:
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!- Hide quoted text -

- Show quoted text -

.



.