Trying to use XLOOKUP and SUMIF function together

[smith46]

hi all

I have tried a few ways to do the task with either sumif or sumifs but they dont work for me. My excel version would be EXCEL 365 ONLINE. As you can see in the photo, my task is trying to sum up the values of the total heads based on the DATE and CUSTOMER.

As you can see in the sheet, the rows on the left hand side highlighted with yellow will keep elaborating as the time goes on, and the columns on the right hand side which are highlighted in blue and black are the summary of each day in each week. I know the data can be summarized by using a pivot table but none of my colleagues know how to use a pivot table so my boss wants me to design a simple sheet for them. The cell in black colour is supposed to show the number of total heads for the company named "Hydro Produce" ; while the cell in blue should show the sum of the total head numbers for the rest of the company. Please note that the summary of the total head should only include the number on that specific date. I have tried something like =Sumifs(sumrange, criteria1(A:A), criteriavalue(Monday,1 July 2014), criteria2(B:B), criteria value("<>Hydro Produce") but the cell returned Spill value. I am thinking of combining XLOOKUP and SUMIF to do the job instead but dont know where to start.

Really appreciate anyone that is able to help. Thank you!!

[Glenn Kennedy]

The simplest way to do this is H2:
=SUM(FILTER($E$2:$E$20,($B$2:$B$20="Hydro Produce")*($A$2:$A$20=G2)))

H3:
=SUM(FILTER($E$2:$E$20,($B$2:$B$20<>"Hydro Produce")*($A$2:$A$20=G3)))

select BOTH cells and copy down.

Change the 20 to something sensible, but future-proof. Do NOT use whole columns unless you have close to 1,000,000 rows of data.

[Glenn Kennedy]

Ignore that... I'm not awake yet.

=SUMIFS(E:E,B:B,"Hydro Produce",A:A,G2)

and

=SUMIFS(E:E,B:B,"<>Hydro Produce",A:A,G3)

select both. Copy down.