Sum Index Issue

[Asheesh]

Hi Guys,

I dont know what part of it i am missing...

I have a table i.e. 3(Rows)*9(Columns) containing boolean values...I then try to sum all the columns and check if the each column sum = 3, then return 1 else FALSE...finally I sum all 1's ..and this should return 2..in the attached example...

It works fine, when I use helper columns..however, when I combine all the formulae into one...it doesnt work as expected...please see the attached for details..

 

[Syedali]

Is this your requirement. Put this formula in F15 =COUNTIFS(F11:N11,">=1")

 

[Asheesh]

Hi Syed...It does yield 2..but I do not want use helper columns...and the criteria_range part of the the countif doesnt support array formula..AFAIK...

 

[NARAYANK991]

Hi Asheesh ,

I am not sure what exactly you want to do , but if you are looking to see how many columns in the data range have all 1s in them , then try this array formula :

=SUM(--(SUBTOTAL(9,OFFSET(F6:F8,,COLUMN($A:$I),3,1))>=3))

Narayan

 

[Asheesh]

Thanks a lot, Precisely, this is what I have been looking for...

 

[John()]

Hi just looking at this again as i wasnt able to figure out a answer earlier so delighted to see NARAYANK991 answer... so downloaded sheet again and added NARAYANK991 formula ... which worked .... but noticed a little error .... if you add 1 to cells to cells F6 to F9 it dose not give a answer of 3 instead of 2 ...
relaised just need to change the cell reference in the formula here OFFSET(F6:F8,,COLUMN .... to E6:E8
Dam wish i could make formulas like this .....