@riny ,
I appreciate your help.
The formula can only sum up ONE column. Every time, you need to make sure the formula sums up the correct date.
Also, what is the sum up the Project column, the formula doesn't work becase the formula looks up the criteria "3rdP", which is share with Actual and Project.
Would you try using SumProducts? I tried it, but it was #value or #Spill
Note: Column A "Cat" criteria is shared with Actual and Project criteria.
Thanks again
Sorry it didn't solve your problem. But please fill in all relevant cells in rows 22:23 manually and explain where the numbers come from. Perhaps I'm slow and just don't understand.
@riny ,
Are you referring to the detail or summary tab ( Prior week comparison)?
Have a good evening
I was referring to the tab where I had entered a formula. So, that's the prior week tab. Based on the data you have in the Daily Bank tab, what do you expect to see in rows 22:23 on the prior week tab if you would do it manually.
Your issue is that the range of cells you want to sum contains text data type, i.e. the blank cells. Fill your blank cells in Daily Bank sheet with 0, then you can for example use below formula in cell C22 in Prior Week Comparison:
=SUMPRODUCT(
('Daily Bank'!$G$10:$P$41)*
('Daily Bank'!$A$10:$A$41=$A22)*
('Daily Bank'!$G$4:$P$4=C$21)*
('Daily Bank'!$G$3:$P$3=$B22)
)
If you are using newer version of Excel you can use SUM, doesn't really matter. I would still opt for a change to have the data in tabular format.
Br,
Anders
@sehlsan ,
Thank you so much for your help with the file. I used the SUMPRODUCT formula you provided, and it worked.
Here is the file -v3 to share.
I very appreciate your time and help.
Have a great evening.
