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 |
#1
|
|||
|
|||
Hi and function help please
My worksheet looks likes this:
A B 1 Name Fruit 2 Sam Apple 3 Sam Apple 4 Sam Apple 5 Sam Orange 6 Jane Orange 7 Jane Apple 8 Jim Orange 9 Jim Orange I would like a function/formula that tells me how many Apples Sam has. thank you! |
#2
|
|||
|
|||
Hi and function help please
Hi!
Try this: =SUMPRODUCT(--(A2:A9="Sam"),--(B2:B9="Apple")) Better to use cells to hold the criteria: D1 = Sam E1 = Apple =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) Biff wrote in message ... My worksheet looks likes this: A B 1 Name Fruit 2 Sam Apple 3 Sam Apple 4 Sam Apple 5 Sam Orange 6 Jane Orange 7 Jane Apple 8 Jim Orange 9 Jim Orange I would like a function/formula that tells me how many Apples Sam has. thank you! |
#3
|
|||
|
|||
Hi and function help please
Thanks Biff!
Doesnt work pasting it in but I am sure it is just that I am a novice.. In your formula, =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) what does the "--" between SUMPRODUCT( and (A2:... represent? Thanks! Andy "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(A2:A9="Sam"),--(B2:B9="Apple")) Better to use cells to hold the criteria: D1 = Sam E1 = Apple =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) Biff wrote in message ... My worksheet looks likes this: A B 1 Name Fruit 2 Sam Apple 3 Sam Apple 4 Sam Apple 5 Sam Orange 6 Jane Orange 7 Jane Apple 8 Jim Orange 9 Jim Orange I would like a function/formula that tells me how many Apples Sam has. thank you! |
#4
|
|||
|
|||
Hi and function help please
Actually, IT DOES work ! THANK YOU !!!!!
wrote in message ... Thanks Biff! Doesnt work pasting it in but I am sure it is just that I am a novice.. In your formula, =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) what does the "--" between SUMPRODUCT( and (A2:... represent? Thanks! Andy "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(A2:A9="Sam"),--(B2:B9="Apple")) Better to use cells to hold the criteria: D1 = Sam E1 = Apple =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) Biff wrote in message ... My worksheet looks likes this: A B 1 Name Fruit 2 Sam Apple 3 Sam Apple 4 Sam Apple 5 Sam Orange 6 Jane Orange 7 Jane Apple 8 Jim Orange 9 Jim Orange I would like a function/formula that tells me how many Apples Sam has. thank you! |
#5
|
|||
|
|||
Hi and function help please
what does the "--" between SUMPRODUCT( and (A2:... represent?
Each of these expressions will return an array of TRUE's or FALSE's: (A2:A9=D1) (B2:B9=E1) The "--" converts those to 1's and 0's. --TRUE = 1, --FALSE = 0 Sumproduct then sums those numbers for the result. See this for a detailed explanation: http://xldynamic.com/source/xld.SUMPRODUCT.html Biff wrote in message ... Thanks Biff! Doesnt work pasting it in but I am sure it is just that I am a novice.. In your formula, =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) what does the "--" between SUMPRODUCT( and (A2:... represent? Thanks! Andy "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(A2:A9="Sam"),--(B2:B9="Apple")) Better to use cells to hold the criteria: D1 = Sam E1 = Apple =SUMPRODUCT(--(A2:A9=D1),--(B2:B9=E1)) Biff wrote in message ... My worksheet looks likes this: A B 1 Name Fruit 2 Sam Apple 3 Sam Apple 4 Sam Apple 5 Sam Orange 6 Jane Orange 7 Jane Apple 8 Jim Orange 9 Jim Orange I would like a function/formula that tells me how many Apples Sam has. thank you! |
Thread Tools | |
Display Modes | |
|
|