Forum Discussion
Solved
COUNTIFS / SUMPRODUCT + OR Alternative?
Table1 columns: A - Order (order number), B - Customer (name), C - Date (shipped), D - Pc (number of boxes shipped) Table2 columns: F - Jan23 (January 2023 dates) through K - Jun23 (June 2023 dates)...
If you use VSTACK rather than OR you will get an array of counts which you could aggregate with SUM or OR
= SUM(COUNTIFS(Table1[Customer], selected, Table1[Date], VSTACK(J3, F4,I5, K7, G8, H8))) = OR(COUNTIFS(Table1[Customer], selected, Table1[Date], VSTACK(J3, F4,I5, K7, G8, H8)))
If you use VSTACK rather than OR you will get an array of counts which you could aggregate with SUM or OR
= SUM(COUNTIFS(Table1[Customer], selected, Table1[Date], VSTACK(J3, F4,I5, K7, G8, H8)))
= OR(COUNTIFS(Table1[Customer], selected, Table1[Date], VSTACK(J3, F4,I5, K7, G8, H8)))- The first formula worked perfectly! I was able to get it functioning for every line and each day of the week.
The second formula doesn't incorporate a final thing that I seem to have forgotten to mention - the values in Table1[Pc] are not all 1; they show the number of boxes shipped, so I'd need each of the amounts added together. That's why I tried SUMIFS instead of COUNTIFS in the original example. Is there a possible fix for that?
Thank you again!I have only had a quick glance but I see no reason to believe that SUMIFS couldn't be used in place of COUNTIFS provided you insert the additional range reference to the data to be summed. That would return and array which would itself need to be summed as before.
