Given 2 Cols, Count # of occurrences vals in same UNHIDDEN row are TRUE (or =1) [using SUMPRODUCT and SUBTOTAL]

[jahsquare]

Hi, I've searched and although some responses here are similar I haven't been able to work a solution.

I have a huge named table with many columns populated with " "s or "1"s. I need to count the number of times, for two given columns, that entries on the same row are both TRUE (or equivalent, or =1 if those would be easier). Right now I have this, which works

=SUMPRODUCT(--(G$2:G$298=$AT$2:$AT$298),--(G$2:G$298<>""),--($AT$2:$AT$298<>""))

however I need this to ignore hidden rows. I believe I need to add SUBOTOTAL(109, .....) somehow, maybe like this:

=SUMPRODUCT(SUBOTOTAL(109,--(G$2:G$298=$AT$2:$AT$298)),--(G$2:G$298<>""),--($AT$2:$AT$298<>""))

But I am not able to figure it out. Do I need to use OFFSET somehow?

Thanks in advance

 

[ctrlcool]

I think this will allow you to get it.

=COUNTIFS(Range1,TRUE,Range2,TRUE) (the TRUE's can be "1" as per your question. Also, you can exclude the hidden rows in a manual fix using this methodology. Add a column and call this Hidden. Put TRUE in the field when the row is hidden and FALSE when the row is not hidden (this won't update automatically, so if you change the filters a lot this probably won't be great). If you do this, Add "Range3,FALSE to the COUNTIFS.

Alternatively, have you tried a Pivot Table?

1. Drop the table into a Pivot Table
2. Set "Design" --> "Report Layout" --> Show in Tabular Form
3. Duplicate your filters
4. Add more filters to limit to the "1" or "True"

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(
     SUBTOTAL(109,OFFSET(G$2,ROW(G$2:G$298)-ROW(G$2),0)),
     --(G$2:G$298=$AT$2:$AT$298),
     --(G$2:G$298<>""),
     --($AT$2:$AT$298<>""))

 

[jahsquare]

Thanks to both of you! Both solutions are helpful. Ctrlcool, that is something i'd never considered.. I need to change the filters often so I was hoping for an automated fix.

Aladin, your code is exactly what I needed. Thanks so much!

Another option would have been to include the filtered text as a condition in the SUMPRODUCT formula i guess, which may make more sense given my application. I have much to learn.

 

[Aladin Akyurek]

Thanks to both of you! Both solutions are helpful. Ctrlcool, that is something i'd never considered.. I need to change the filters often so I was hoping for an automated fix.

Aladin, your code is exactly what I needed. Thanks so much!

Another option would have been to include the filtered text as a condition in the SUMPRODUCT formula i guess, which may make more sense given my application. I have much to learn.

You are welcome. If the filter value is not changing at will, you can include a conditional term regarding that filter directly in the formula instead of the SUBTOTAL term.