SumProduct function

[Vicky]

I have 6 columns, which looks like this, I want the sum of all the Ideal time
and Achieved time for A01, A02 and A03 respectively

ID Ideal time achieved time Ideal time Achieved time IDeal time
achieved time
A01 45 50 35 30
30 50
A02 50 35 37 33
25 41
A03 35 25 40 35
60 55

Thanks in Advance!!
Vicky

 

[muddan madhu]

Try this

=SUMPRODUCT((B1:G1="ideal time")*(A2:A4="A01")*(B2:G4))

 

[Vicky]

It is returning me the #value error.

Thx,
Vicky

Try this

=SUMPRODUCT((B1:G1="ideal time")*(A2:A4="A01")*(B2:G4))

 

[Vicky]

finally got the answer, i did it on my own.

Thx
Vicky

 

[Roger Govier]

Hi Vicky

there is nothing wrong with the formula, it must be your data.
To have a #Value error, I would suspect that you have some Text within the
data area

 

[Roger Govier]

and the answer was ...... ?

 

[Vicky]

Sorry for replying late,

I used the formulat =SUMPRODUCT(--($B$1:$G$1=$I$1),B2:G2)
Ideal time Achieved time Ideal time Achieved time Ideal time Achieved
time Ideal time Achieved time
A01 45 50 35 30 30 50 110
A02 50 35 37 33 25 41 112
A03 35 25 40 35 60 55 135

Thx,
Vicky