| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct and if statement
I have the following formula that is not working as I would like it to.
=IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
I don't understand the significance of (say) Shot 3 appearing more than once
in Col V. Are you saying the numbers associated with it in AA & AG. If so then this formula may not be what you require because it finds the first instance of what is in A28. having said that try:- =VLOOKUP(A28,V1:AA20,6,FALSE)&" Shots "&VLOOKUP(A28,V1:AG20,12,FALSE)&" Days" Mike "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
I think what you are looking for is this:
=SUMPRODUCT(--($V$9:$V$90=$A$28),$AA$9:$AA$90,$AG$9:$AG$90) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
Try:
=SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90)) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
sorry i should have given more info.
if cell A28 equals a cell in col V then multiply the corresponding cell in col AA by col AG does that make sense. the shot type may appear more than once as it is broken into different sections of a very big job. "JLatham" wrote: I think what you are looking for is this: =SUMPRODUCT(--($V$9:$V$90=$A$28),$AA$9:$AA$90,$AG$9:$AG$90) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
sorry i should have explained better.
if cell A28 equals a cell in col. V then mulitply the corresponding cell in col. AA by the corresponding cell in col AG. does that make sense? the reason that shot type appears in the list more than once is that each type of shot appears in different sections of a very big job. ellebelle "Toppers" wrote: Try: =SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90)) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
did you try the formula? .... because that's this does ...
=SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90) "ellebelle" wrote: sorry i should have explained better. if cell A28 equals a cell in col. V then mulitply the corresponding cell in col. AA by the corresponding cell in col AG. does that make sense? the reason that shot type appears in the list more than once is that each type of shot appears in different sections of a very big job. ellebelle "Toppers" wrote: Try: =SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
That is exactly what the formula that both Toppers and I have provided does.
The --($V$9:$V$90)=$A$28 portion will end up returning either 0 (does not match) or 1 (does match) for each row from 9 to 90, and will do the multiply AA# by AG# at each row - but when V#<A28, the result of that is zero, so AA#*AG#*0 = 0. Try it, you'll like it! "ellebelle" wrote: sorry i should have given more info. if cell A28 equals a cell in col V then multiply the corresponding cell in col AA by col AG does that make sense. the shot type may appear more than once as it is broken into different sections of a very big job. "JLatham" wrote: I think what you are looking for is this: =SUMPRODUCT(--($V$9:$V$90=$A$28),$AA$9:$AA$90,$AG$9:$AG$90) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumproduct and if statement
this worked perfectly - thanks!
"Toppers" wrote: did you try the formula? .... because that's this does ... =SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90) "ellebelle" wrote: sorry i should have explained better. if cell A28 equals a cell in col. V then mulitply the corresponding cell in col. AA by the corresponding cell in col AG. does that make sense? the reason that shot type appears in the list more than once is that each type of shot appears in different sections of a very big job. ellebelle "Toppers" wrote: Try: =SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90) "ellebelle" wrote: I have the following formula that is not working as I would like it to. =IF($V9:$V90=A28,SUMPRODUCT($AA$9:$AA$90,AG9:AG90) ) I have three columns of data: col. V has type of shot (shot1, shot 2, shot 3 etc.) col. AA has number of shots per type (4, 5,2 etc) col. AG has number of days each type of shot should take to finish (3, 5, 19 etc) I would like to enter the shot type name into cell A28 and retireve hte total number of days that the shot type requires. Pls note that a shot type can appear in the col V more than once. any ideas? e |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT problem, using OR within statement | Excel Worksheet Functions | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| Multiple if criteria in one statement (if/sumproduct) | Excel Discussion (Misc queries) | |||
| sumproduct formula too long, & how to use make an 'OR' statement w | Excel Worksheet Functions | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
Linear Mode
