If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 - |
#12
|
|||
|
|||
Count cells based upon criteria in other cells
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 - . |
#13
|
|||
|
|||
Count cells based upon criteria in other cells
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 - . |
#14
|
|||
|
|||
Count cells based upon criteria in other cells
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 - |
#15
|
|||
|
|||
Count cells based upon criteria in other cells
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 - . . |
|
Thread Tools | |
Display Modes | |
|
|