I am having significant issues trying to resolve my problem. Essentially I need an excel formula that replicates a SUMIFS function, as it appears that sumifs doesn't work in my scenario. Effectively I need to SUM across a horizontal axis, based on the date & header parameter. I have tried summing index-matches, sumifs, aggregates, summing sumif, summing vlookups & hlookups, and I either get errant values or I get the first value (for example, store A would return 0 for 7/8 & Store G would return -3,291)
=SUMIF($1:$1,B22,INDEX($C$2:$AQ$1977,1,MATCH($A982,$A$2:$A$9977,0)))
=SUMIFS(B2:N2,1:1,B22,A:A,A23)
=SUM(SUMIF($B$1:$N$1,$B23,INDEX($B$2:$N$12,1,MATCH($A23,$A$2:$A$12,0))),SUMIF($B$1:$N$1,$B23,INDEX($B$2:$N$12,2,MATCH($A23,$A$2:$A$12,0))),SUMIF($B$1:$N$1,$B23,INDEX($B$2:$N$23,3,MATCH($A23,$A$2:$A12,0)))).
I'm sure the sumrange is what is killing me, but I would ideally like the code to be dynamic enough to locate and sum the cells via references, in case the input data changes at some point. I am working with thousands of rows so the sum range is B2:AQ10000.
The formula is on a different sheet than this but i input it as an example.
What am I missing? Is there a way to do this with Excel?
