Sumproduct Returns Value Error

[annie46]

I have a sheet that contains 3 columns for each day of the week.
Hours, Code # (10-636), Category (ABC)
I need weekly sums of the total hours by category and a weekly sum of hours by code #.
This formula works for the latter
=SUMPRODUCT(--(F9:A28=10),--B9:AT28
but if I try to input a letter in the category column it gives a value error.
I have been trying variations of this for 3 days with no luck, any advice would be greatly appreciated.

Thank you,

[NBVC]

You may need to revert to a Sum(IF()) array formula setup in order to avoid the text entries.....

Can we see a zipped sample of your sheet?

[annie46]

Please see the attachment. The formula does work but only if no text is in that range.

I have tried SumIf and it didn't work, but of course I am still a novice when it comes to excel.

Thanks again

[NBVC]

No attachment can be seen...

make sure you zip it first and that it is less than 100kb in size.

Then after you located the zipped file, click Upload. Then close.

[annie46]

Hopefully it worked this time

[NBVC]

First. You must be consistent with your raw data organization...

For Tuesday you've got the Code and Category columns switched... please fix that first.

Then in B31 use formula:

=SUMPRODUCT(($F$9:$BG$28=D31)*ISNUMBER($B$9:$BC$28),$B$9:$BC$28)

copied down. Then you can copy this group to the next group and so on horizontally.

and in F47:

=SUMPRODUCT(($J$9:$BK$28=B47)*ISNUMBER($B$9:$BC$28),$B$9:$BC$28)

copied down.

[daddylonglegs]

=SUMPRODUCT(--(F9:A28=10),--B9:AT28

This formula doesn't look right, the ranges need to be the same size. Can you post the correct formula?