SUMIFS #VALUE! error

[adrienne0914]

Trying to do a relatively simple SUMIFS formula, but getting a #VALUE ! error. I want to sum the weeks where YTD is on row 1:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]YTD[/TD]
[TD="align: center"]YTD[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Week 1[/TD]
[TD="align: center"]Week 2[/TD]
[TD="align: center"]Week 3[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD]All COLORS[/TD]
[TD]3827[/TD]
[TD]3666[/TD]
[TD]3997[/TD]
[TD]11490[/TD]
[/TR]
[TR]
[TD]Total BLUE[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Total GREEN[/TD]
[TD]50[/TD]
[TD]65[/TD]
[TD]22[/TD]
[TD]138[/TD]
[/TR]
[TR]
[TD]Total YELLOW[/TD]
[TD]71[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]167[/TD]
[/TR]
[TR]
[TD]Total RED[/TD]
[TD]52[/TD]
[TD]39[/TD]
[TD]71[/TD]
[TD]162[/TD]
[/TR]
</tbody>[/TABLE]

Expected results:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]YTD[/TD]
[/TR]
[TR]
[TD]ALL COLORS[/TD]
[TD="align: center"]7493[/TD]
[/TR]
[TR]
[TD]Total BLUE[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]Total GREEN[/TD]
[TD="align: center"]115[/TD]
[/TR]
[TR]
[TD]Total YELLOW[/TD]
[TD="align: center"]119[/TD]
[/TR]
[TR]
[TD]Total RED[/TD]
[TD="align: center"]91[/TD]
[/TR]
</tbody>[/TABLE]


=SUMIFS($B$3:$D$7,$A$3:$A$7,A19,$B$1:$D$1,B17)

 

[Taul]

HI,
Try this approach

'=SUMPRODUCT(($B$1:$D$1=$B$10)*($A$3:$A$7=$A11),$B$3:$D$7)

Paul.

 

[jtakw]

Hi,

Same as above, was working on it during the above post:

Book1
	A	B	C	D	E
1		YTD	YTD
2		Week 1	Week 2	Week 3	Total
3	All COLORS	3827	3666	3997	11490
4	Total BLUE	0	0	6	6
5	Total GREEN	50	65	22	138
6	Total YELLOW	71	48	48	167
7	Total RED	52	39	71	162
8
17		YTD
18
19	ALL COLORS	7493
20	Total BLUE	0
21	Total GREEN	115
22	Total YELLOW	119
23	Total RED	91
Sheet150

Cell Formulas
Range	Formula
B19	=SUMPRODUCT((B$1:D$1=B$17)*(A$3:A$7=A19)*B$3:D$7)

B19 formula copied down.

 

[adrienne0914]

SUMPRODUCT works great in my example file where all info is on one tab. But in my actual file, which references another tab with tons of data, I get an #N/A message. What are some things that could be wrong? Here is my formula:

=SUMPRODUCT(('Booking Report'!$DG$27:$HG$27=B$7)*(Bookings!$A$33:$A$67=$A9)*Bookings!$DG$35:$HG$67)

 

[jtakw]

You can't "split up" the criterion into different sheets with SUMPRODUCT, you either have to find another way to include some kind of condition in Bookings! sheet, or copy 'Booking Report'!DG27:HG27 to Bookings!

 

[adrienne0914]

I didn't even notice. That was a typo. I changed Booking Report! to Bookings! and it worked. THANK YOU!

 

[jtakw]

Great, that was a simple fix.

You're welcome.