SOLVED

Excel 365 - Count visible rows with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1427656%22%20slang%3D%22en-US%22%3EExcel%20365%20-%20Count%20visible%20rows%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427656%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20racked%20my%20brain%20trying%20to%20find%20the%20solution%20to%20this%20but%20no%20dice%20so%20I'm%20coming%20to%20you!%20Here%20are%20the%20details%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B*%20Count%20how%20many%20WIDGETS%20(simple%20enough)%3C%2FP%3E%3CP%3E%26nbsp%3B*%20Two%20potential%20names%20that%20are%20similar%3B%20'WIDGETS'%20and%20'WIDGETS%20-%20WITH%20CHEESE'%3C%2FP%3E%3CP%3E%26nbsp%3B*%20But%20only%20for%20the%20rows%20displayed%2C%20not%20those%20rows%20that%20are%20filtered%20out%20or%20hidden%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20this%20formula%20(see%20below)%20and%20it%20was%20working%20until%20I%20realized%20'WIDGETS'%20%26amp%3B%20WIDGETS%20-%20WITH%20CHEESE'%20should%20be%20counted%20together%20because%20at%20the%20end%20of%20the%20day%20both%20products%20fall%20under%20the%20same%20parent%20category%20of%20'WIDGETS'.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DSUMPRODUCT((G8%3AG5000%3D'WIDGETS')*(SUBTOTAL(103%2COFFSET(G8%2CROW(G8%3AG5000)-MIN(ROW(G8%3AG5000))%2C0))))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20a%20number%20of%20options%20from%20OR%20to%20COUNTIFS%20to%20SUM%20but%20nothing%20that%20I'm%20coming%20up%20with%20is%20working%2C%20and%20I%20would%20greatly%20appreciate%20any%20help%20you%20can%20offer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20we're%20talking%20about%20this%20formula%2C%20specifically%2C%20can%20I%20not%20use%20Table%20references%20in%20an%20array%20formula%2C%20or%20even%20a%20Named%20Range%3F%20I%20was%20trying%20to%20replace%20'G8%3AG5000'%20with%20Named%20Range%20'PROD'%2C%20where%20I%20sent%20the%20range%20as%20G8%3AG5000.%20Only%20taking%20this%20route%20because%20I've%20seen%20where%20array%20formulas%20have%20limits%20in%20terms%20of%20references%20to%20whole%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20I'm%20done%20and%20would%20appreciate%20any%20help%20you%20can%20offer!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E**%20If%20there%20is%20a%20better%20%2F%20simpler%20%2F%20different%20formula%20you%20would%20recommend%20I%20am%20completely%20open%20to%20that%20too!%20**%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1427656%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Desktop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427885%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Count%20visible%20rows%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F681480%22%20target%3D%22_blank%22%3E%40RLCornish00%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20we%20need%20to%20implement%20OR%20condition%20to%20calculate%20both%20options.%20For%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20313px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195471i9F6AD78608AF3295%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewe%20may%20use%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%20(%20(G8%3AG5000%3D%22WIDGETS%22)%2B(G8%3AG5000%3D%22WIDGETS%20-%20WITH%20CHEESE%22))*(SUBTOTAL(103%2COFFSET(G8%2CROW(G8%3AG5000)-MIN(ROW(G8%3AG5000))%2C0))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efiltered%20result%20will%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20302px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195472iFDB5ABF958E4844D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20could%20be%20with%20helper%20column%20C%2C%20where%20we%20add%20the%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAGGREGATE(3%2C5%2CF8)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20returns%20zero%20for%20hided%20rows%20and%201%20for%20visible.%20Formula%20to%20sum%20will%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((%20(G8%3AG5000%3D%22WIDGETS%22)%2B(G8%3AG5000%3D%22WIDGETS%20-%20WITH%20CHEESE%22))*H8%3AH5000)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAbove%20is%20regular%20one%20(non-array)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1434481%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20-%20Count%20visible%20rows%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%20this%20is%20fantastic!%20I%20tried%20and%20tried%20to%20figure%20out%20the%20OR%20option%20and%20after%20exhausting%20my%20brain%20figured%20there%20was%20someone%20smarter%20that%20might%20be%20willing%20to%20help%2C%20and%20here%20you%20did!%20Thank%20you%2C%20I'm%20most%20appreciative.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,

 

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'.

 

{=SUMPRODUCT((G8:G5000='WIDGETS')*(SUBTOTAL(103,OFFSET(G8,ROW(G8:G5000)-MIN(ROW(G8:G5000)),0))))}

 

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! **

2 Replies
Highlighted
Solution

@RLCornish00 

Here we need to implement OR condition to calculate both options. For such sample

image.png

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

image.png

 

Another variant could be with helper column C, where we add the formula

=AGGREGATE(3,5,F8)

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)

Highlighted

@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.