Confused in Sumproduct Formula

[nkunni]

Dear Friends,

Good Day for all,

I work with sumproduct formul, I just need to omit some count from a
column. For reference attached sample file.

 

[NARAYANK991]

Hi ,

I am not sure about your requirement , but try the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(IF($B$24:$B$88=$L$2, IF($F$24:$F$88=K9, 1/COUNTIFS($B$24:$B$88, $L$2, $F$24:$F$88, K9, $C$24:$C$88, $C$24:$C$88))))

Narayan

 

[Chihiro]

Using same concept, SUMPRODUCT will become...
Cell L7
=SUMPRODUCT(--($B$24:$B$88=$L$2),--($F$24:$F$88=$N$2),1/COUNTIF($C$24:$C$88,$C$24:$C$88))

Validated against L9:L14
=SUMPRODUCT(--($B$24:$B$88=$L$2),--($F$24:$F$88=K9),1/COUNTIF($C$24:$C$88,$C$24:$C$88))

 

[nkunni]

Using same concept, SUMPRODUCT will become...
Cell L7
=SUMPRODUCT(--($B$24:$B$88=$L$2),--($F$24:$F$88=$N$2),1/COUNTIF($C$24:$C$88,$C$24:$C$88))

Sir,

It works perfectly..
Thanks a lot.

 

[nkunni]

Hi ,

I am not sure about your requirement , but try the following array formula , to be entered using CTRL SHIFT ENTER :

This formula works perfectly, Thank you Sir.