05-29-2020 01:06 PM
05-29-2020 01:06 PM
I have racked my brain trying to find the solution to this but no dice so I'm coming to you! Here are the details:
* Count how many WIDGETS (simple enough)
* Two potential names that are similar; 'WIDGETS' and 'WIDGETS - WITH CHEESE'
* But only for the rows displayed, not those rows that are filtered out or hidden
I found this formula (see below) and it was working until I realized 'WIDGETS' & WIDGETS - WITH CHEESE' should be counted together because at the end of the day both products fall under the same parent category of 'WIDGETS'.
I have tried a number of options from OR to COUNTIFS to SUM but nothing that I'm coming up with is working, and I would greatly appreciate any help you can offer.
While we're talking about this formula, specifically, can I not use Table references in an array formula, or even a Named Range? I was trying to replace 'G8:G5000' with Named Range 'PROD', where I sent the range as G8:G5000. Only taking this route because I've seen where array formulas have limits in terms of references to whole columns.
Ok, I'm done and would appreciate any help you can offer!
** If there is a better / simpler / different formula you would recommend I am completely open to that too! **
05-29-2020 02:42 PMSolution
Here we need to implement OR condition to calculate both options. For such sample
we may use
=SUMPRODUCT( ( (G8:G5000="WIDGETS")+(G8:G5000="WIDGETS - WITH CHEESE"))*(SUBTOTAL(103,OFFSET(G8,ROW(G8:G5000)-MIN(ROW(G8:G5000)),0))))
filtered result will be
Another variant could be with helper column C, where we add the formula
which returns zero for hided rows and 1 for visible. Formula to sum will be
=SUMPRODUCT(( (G8:G5000="WIDGETS")+(G8:G5000="WIDGETS - WITH CHEESE"))*H8:H5000)
Above is regular one (non-array)
06-02-2020 01:22 PM
@Sergei Baklan, this is fantastic! I tried and tried to figure out the OR option and after exhausting my brain figured there was someone smarter that might be willing to help, and here you did! Thank you, I'm most appreciative.