Can't determine why I'm getting #Value for Sumifs function

[jlheath01]

I'm trying to calculate total walking distances for users by week when I have daily data.

In row 2, I have the week number and in column A, I have the users. In cells b3:BS10, I have their actual miles.

Named ranges are: DailyWeek (B2:BS2), DailyMiles (B3:BS10), Walkers (A3:A10)

My weeks and walkers are formatted as text and my miles are formatted as number

My formula is this: =SUMIFS($B$3:$BS$10,DailyWeek,"1",A3:A10,"XXX") and I continue to get a #Value error, but I don't understand why if everything is formatted the way it should be. Please help!

My workbook is attached.

[Jonmo1]

The ranges must be the same dimensions in Sumifs..

You have 2 ranges going accross 1 row from column B to BS, but the last range is going down a single column from row 3 to 10.
Can't do that..

Try
=SUMPRODUCT((DailyWeek=1)*(A3:A10="XXX")*($B$3:$BS$10))

[Pete_UK]

Put this formula in B13:

=SUMPRODUCT($B$3:$BS$10,(DailyWeek=--SUBSTITUTE(LEFT(B$12,FIND("-",B$12)-1),"Week",""))*($A$3:$A$10=$A13))

then you can copy this across and down that lower summary table. Note that your week numbers in row 2 are numerical values (though the cells are formatted as text), hence the need for the -- in the middle of the formula, but if you change the week numbers then you will need to omit this part.

Hope this helps.

Pete