|
SUMIF PROBLEM
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 |
SUMIF PROBLEM
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) "Malcolm Austin" wrote in message ... 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 |
SUMIF PROBLEM
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) "Ron Coderre" wrote in message ... 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) "Malcolm Austin" wrote in message ... 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 |
SUMIF PROBLEM
HI Ron,
got 5 mins off from grandkids to look at computer. I'll revert on this when they've gone to bed! Malcolm... "Ron Coderre" wrote in message ... 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) "Ron Coderre" wrote in message ... 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) "Malcolm Austin" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com