Count cells based upon criteria in other cells

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:I1000)*(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:I1000)*(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 -

.



.