Andreamary
asked on
Refining my clunky SUMIF formula referencing an Excel table
I am just learning to work with formulas in Excel tables. The following formula that I created works, but seems clunky having to duplicate the range criteria, and I have not been able to successfully streamline it:
=SUMIF(Table1[WO1],$A2,Tab
My attempts have caused errors in the sum_range:
=SUMIF(Table1[WO1],$A299,T
Is it possible to simplify the formula so that SUMIF only has to be entered once?
Thanks,
Andrea
=SUMIF(Table1[WO1],$A2,Tab
My attempts have caused errors in the sum_range:
=SUMIF(Table1[WO1],$A299,T
Is it possible to simplify the formula so that SUMIF only has to be entered once?
Thanks,
Andrea
if you need to sum 2 different columns, you will need to use the "+" operator
ASKER
I've attached a sample of the workbook. Sheet1 contains the Excel table. Sheet2 is a roll-up of Sheet1, which is to sum all the minutes worked per work order (1 through 5). I've started by totalling WO1, and Sheet2, Col B contains the formula that I am trying to simplify:
=SUMIF(Table1[WO1],$A2,Tab
Hope this helps,
Andrea
EE_SUMIF.xlsm
=SUMIF(Table1[WO1],$A2,Tab
Hope this helps,
Andrea
EE_SUMIF.xlsm
well, i guess your original formula could be the simplest based on your requirements and it cannot be further simplified since you're summing 2 different ranges.
=SUMIF(Table1[WO1],$A2,Table1[WO1_Process_Min])+SUMIF(Table1[WO1],$A2,Table1[WO1_RWK_Min])
This is the formula using SUMIF for your WO1
and this one is for all your WO's as you indicated in your question you will see the total in Col C of sheet2
check Col D
I put in Col C the total as you did for each column to verify its the same as D
chk the file
gowlfow
EE_SUMIF-V01.xlsm
=SUMIF(Table1[WO1],"="&$A2,Table1[[WO1_Process_Min]:[WO1_RWK_Min]])and this one is for all your WO's as you indicated in your question you will see the total in Col C of sheet2
=SUMIF(Table1[WO1],"="&$A2,Table1[[WO1_Process_Min]:[WO5_RWK_Min]])check Col D
I put in Col C the total as you did for each column to verify its the same as D
chk the file
gowlfow
EE_SUMIF-V01.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works perfectly - thanks, Shums!
Thanks, Gowflow, for your response - in reviewing it for WO1 it appeared to give me the total minutes for [WO1_Process_Min] only, and didn't include the RWK minutes. For my purposes, I needed the separate totals for each of the 5 WOs.
Cheers,
Andrea
Thanks, Gowflow, for your response - in reviewing it for WO1 it appeared to give me the total minutes for [WO1_Process_Min] only, and didn't include the RWK minutes. For my purposes, I needed the separate totals for each of the 5 WOs.
Cheers,
Andrea
ASKER
Very pleased. :-)
You're Welcome Andrea! Pleased to help :)
SUMIFS function allows you to put multiple criteria