Forum Discussion
SUMIFS with Criteria on Horizontal and Vertical Axis
Hi,
Please I need assistance with SUMIFS function to get values between two dates which are on horizontal axis and a third criteria which is on the vertical axis.
As shown in the attached image, I want to retrieve the sum of 'Weekly EPOS Units' from the table H2:N14 into column F3:F8 that fall between the dates in columns D and E, and with the Product ID in column C.
I get the #VALUE! working with the data in this format.
Thanks for your help!
OlawaleSheks Try SUMPRODUCT instead. In cell F3:
=SUMPRODUCT(($I$2:$N$2>=D3)*($I$2:$N$2<=E3)*($H$3:$H$14=C3)*$I$3:$N$14)Copy down as needed.
6 Replies
Wondering if any of you can help ..new to the community. Here goes trying to use sumifs but I cannot figure out what I am doing wrong. I just want to sum the distribution accts by state.
=SUMIFS(B9:I69,A9:A69,A78,B7:I7,B76)
Thank you in advance for any help.
SUMIFS() works on the same size ranges. Use SUMPRODCT() instead as in djclements​ answer in this thread. Something like
Instead of SUMIFS, use SUMPRODUCT (if you have Microsoft 365 or Office 2021, SUM will work as well).
=SUMPRODUCT(($H$3:$H$14=C3)*($I$2:$N$2>=D3)*($I$2:$N$2<=E3)*$I$3:$N$14)
- Thanks a lot HansVogelaar
OlawaleSheks Try SUMPRODUCT instead. In cell F3:
=SUMPRODUCT(($I$2:$N$2>=D3)*($I$2:$N$2<=E3)*($H$3:$H$14=C3)*$I$3:$N$14)Copy down as needed.
- djclements, this works. Thanks very much for your help!
