Log In
Register
Home
August 8, 2020
Offline
Hi,
I am looking to keep track of an associates daily tasks. I want to keep track of that in a summary sheet for the entire week.
I am looking to see if that person is on a certain job A OR job B. They may also not start on Job A or B but may switch to that later. If so then add the data in this range of cells.
What is happening is that the number of cells that I am comparing is a mismatch to the cells that I want to sum when trying to use a sumIfs statement.
So I had to compared each cell to each condition for each day of the week.
=(((SUMIF(P78,BALE,AF78))+(SUMIF(R78,BALE,AF78))+(SUMIF(T78,BALE,AF78)))+(((SUMIF(AI78,BALE,AY78))+(SUMIF(AK78,BALE,AY78))+(SUMIF(AM78,BALE,AY78)))+(((SUMIF(BB78,BALE,BR78))+(SUMIF(BD78,BALE,BR78))+(SUMIF(BF78,BALE,BR78)))+(((SUMIF(BU78,BALE,CK78))+(SUMIF(BW78,BALE,CK78))+(SUMIF(BY78,BALE,CK78)))+(((SUMIF(CN78,BALE,DD78))+(SUMIF(CP78,BALE,DD78))+(SUMIF(CR78,BALE,DD78)))+(((SUMIF(DG78,BALE,DW78))+(SUMIF(DI78,BALE,DW78))+(SUMIF(DK78,BALE,DW78)))+(((SUMIF(DZ78,BALE,EP78))+(SUMIF(EB78,BALE,EP78))+(SUMIF(ED78,BALE,EP78)))))))))) +(((SUMIF(P78,PALL,AF78))+(SUMIF(R78,PALL,AF78))+(SUMIF(T78,PALL,AF78)))+(((SUMIF(AI78,PALL,AY78))+(SUMIF(AK78,PALL,AY78))+(SUMIF(AM78,PALL,AY78)))+(((SUMIF(BB78,PALL,BR78))+(SUMIF(BD78,PALL,BR78))+(SUMIF(BF78,PALL,BR78)))+(((SUMIF(BU78,PALL,CK78))+(SUMIF(BW78,PALL,CK78))+(SUMIF(BY78,PALL,CK91)))+(((SUMIF(CN78,PALL,DD78))+(SUMIF(CP78,PALL,DD78))+(SUMIF(CR78,PALL,DD78)))+(((SUMIF(DG78,PALL,DW78))+(SUMIF(DI78,PALL,DW78))+(SUMIF(DK78,PALL,DW78)))+(((SUMIF(DZ78,PALL,EP78))+(SUMIF(EB78,PALL,EP78))+(SUMIF(ED78,PALL,EP78))))))))))
July 16, 2010
Offline
Hi Leslie,
The reason you're having problems is because the file is not structured correctly. In order to use SUMIF your data should be in a tabular format. Your data is already in what I call a semi-report. i.e. you've skipped the tabular data step and jumped straight to a report style layout. This is sometimes easier for the purpose of data entry, but when it comes time to summarising and analysing data, it becomes a nightmare...as illustrated by the SUMIF formula above! 🙂
I would use Power Query to get the data you're wanting to summarise and consolidate it into a tabular format so the formulas can be used the way they were intended. If you keep the file the way it is you're destined for formula errors. It's just too risky.
This tutorial shows you how to unpivot data using Power Query, which is what you'll need to do, but for multiple tables which you will then want to append to one another to make one big table that contains all of the data you want to summarise in the SUMIF fomula example above.
I hope that points you in the right direction. If you get stuck with Power Query, please come back and raise a new ticket with your Excel file containing the query.
Mynda
1 Guest(s)
