| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Hybrid Mode
