Sumifs Help

[Joneye]

Hello helpful people

I've writtern a sumifs formula that i know i have incorrect, In Q1, for the Channel PL id like to add up the PO values.

in short i'd like to underdstand

1. Why im wrong
2. is the 'sumifs' formula correct
3. If alternative formula - im all ears

FORMULA IN Cell #
=SUMIFS(C3:N6,C1:N1,B8,A3:A6,A8,B3:B6,A9)

		Q1	Q1	Q1	Q2	Q3	Q4
CHANNEL	Remark	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
PL	Forecast	1,545	0	8,999	10,000	59,785	0	0	556,633	1,233	15,000	0	0
PL	PO	2,000	2,005	6,988	0	75,000	0	0	25,000	500	7,850	0	0
PL	Split	455	2,005	(2,011)	(10,000)	15,215	0	0	(531,633)	(733)	(7,150)	0	0
PL	Probability	25%	35%	50%	50%	100%	25%	25%	95%	75%	50%	50%	50%
PL	Q1
PO	#VALUE!​

 

[myall_blues]

It's wrong because (as per documentation):

Your first criteria_range argument (C1:N1) does not meet that requirement.
An alternative is:
=SUMPRODUCT(($C$1:$N$1=B8)*($A$3:$A$6=A8)*($B$3:$B$6=A9)*$C$3:$N$6)

 

[myall_blues]

Another alternative for 365 is:

=SUM(DROP(FILTER(VSTACK(B1:N1,FILTER(B1:N6,(A1:A6=A8)*(B1:B6=A9))),B1:N1=B8),1))

 

[Joneye]

Super thanks for the explanation and the formula, really helps cheers Sir

 

[Peter_SSs]

Another alternative for 365 is:

=SUM(DROP(FILTER(VSTACK(B1:N1,FILTER(B1:N6,(A1:A6=A8)*(B1:B6=A9))),B1:N1=B8),1))

Not sure why you are VSTACKing row 1 on and then DROPping it off again later?

=SUM(FILTER(FILTER(C3:N6,(A3:A6=A8)*(B3:B6=A9)),C1:N1=B8))

 

[myall_blues]

Not sure why you are VSTACKing row 1 on and then DROPping it off again later?

=SUM(FILTER(FILTER(C3:N6,(A3:A6=A8)*(B3:B6=A9)),C1:N1=B8))

One of those moments us mere mortals have from time to time.

 

[Peter_SSs]

One of those moments us mere mortals have from time to time.

 

[Joneye]

Huge thanks to all the options and help, really informative and i leave solving a probelm and learning something new. Cheers one and all

 

[Peter_SSs]

Glad we were able to help. Thanks for the follow-up.