SUMIF PROBLEM

[Malcolm Austin]

My formula is:- =SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3,'INPUT
PAGE - FLYING TIMES'!$H$4:$J$23)

D4 to D23 is the part of my data which I'm looking for the answer in D3
(aeroplane type).
H4:J23 is where I looking for a numerical figures (flying time in minutes)

This is working well for the first column, (H) but is not taking note of the
2nd and 3rd column.

1/ Am I using the correct formula to pick data from 3 columns?
2/ If correct what should I do to the formula to make it work across all 3?
3/ If not, which one should I go for?


Malcolm

 

[Ron Coderre]

You have a couple options:

Multiple SUMIF's...
=SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$H$4:$H$23)+
SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$H$4:$I$23)+
SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$H$4:$J$23)

or...
=SUMPRODUCT(('INPUT PAGE - FLYING TIMES'!$D$4:$D$23=
D3)*'INPUT PAGE - FLYING TIMES'!$H$4:$J$23)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Darn! Sloppy editing of the range refs in the multiple SUMIF's..
It should be:
=SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$H$4:$H$23)+
SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$I$4:$I$23)+
SUMIF('INPUT PAGE - FLYING TIMES'!$D$4:$D$23,D3
,'INPUT PAGE - FLYING TIMES'!$J$4:$J$23)

Apologies.

(I'd go with the SUMPRODUCT approach, though)

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Malcolm Austin]

HI Ron,
got 5 mins off from grandkids to look at computer. I'll
revert on this when
they've gone to bed!

Malcolm...