sumproduct and if statement

[ellebelle]

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

 

[Mike H]

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

 

[JLatham]

I think what you are looking for is this:
=SUMPRODUCT(--($V$9:$V$90=$A$28),$AA$9:$AA$90,$AG$9:$AG$90)

 

[Toppers]

Try:

=SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90))

 

[ellebelle]

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.

 

[ellebelle]

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]

did you try the formula? .... because that's this does ...

=SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90)

 

[JLatham]

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]

this worked perfectly - thanks!

did you try the formula? .... because that's this does ...

=SUMPRODUCT(--($V9:$V90=A28),$AA$9:$AA$90,AG9:AG90)