Value Error Excel SUMIF and SUMProduct formulas horizontal & vertical criteria

Hi All,

I am trying to add number of days based on a period and a category. My criteria ranges (Category and Period) and Data are on one tab and vertical. My criterias are on a seperate tab on the spreadsheet with "Category" being vertical and "Period" being horizontal.

I have tried to use both of the SUMIF and SUMProduct formulas but none of these work and are bringing an error that I am unable to correct. The formatting of the cells are similar so that would not be the issue I think.

=SUMIFS(Timesheet!R6:R673,Timesheet!P6:Q673,Summary!B4:B62,Timesheet!J6:L673,Summary!C3:AO3)
=SUMPRODUCT(Timesheet!P6:Q673=Summary!B4)*(Timesheet!J6:L673=Summary!$C$3)*Timesheet!R6:R673

Please can you help?

Thanks a Lot

Hi Rorya,

Thanks for this.

Now I have updated the SUMPRODUCT formula however I have an NA error now.

With regards to the SUMIF, I have the same range sizes (row 6 to row 673)so I am unsure.

I have tried now the below formula with arrays but the formula is only working on the first column, the remaining ones are not updating.

=SUM((Timesheet!P6:P673=Summary!B4)*(Timesheet!J6:L673=Summary!$C$3)*(Timesheet!R6:R673))


Thank you

Ok Please find attached a sample file.

Thanks

Thank you!

Thank you Rorya, it works now.

Kind Regards