Sum Product & Data Filter

[dramnath1981]

Similar to CountIF & SumIF, how good it will be if we have SumProductIF!!!!

Likewise, in data filter it would be of great help if we have an inverse selection option. For ex: if we have selected 3 out of 10 values in the filter and now we need to select the rest 7 values, its manual and time consuming.

Many a times, I feel handicapped that we don't have functions like this.

Please suggest if you have any alternatives.

Thanks a lot.

 

[Khalid NGO]

Similar to CountIF & SumIF, how good it will be if we have SumProductIF!!!!

Hi,

Although there is no SUMPRODUCTIF( but we can implement same, and sometime even better logics (i.e. mixed, 2D arrays) with SUMPRODUCT.

for example following SUMIF and SUMPRODUCT produces same output:

=SUMIF(A1:A10,B1,C1:C10)

=SUMPRODUCT((A1:A10=B1)*(C1:C10))

Likewise, in data filter it would be of great help if we have an inverse selection option. For ex: if we have selected 3 out of 10 values in the filter and now we need to select the rest 7 values, its manual and time consuming.

Have you tried this?


Regards,

 

[dramnath1981]

Thank you Khalid.

I have a different issue and I hope the attachment will make it clear. Please help.

 

[Khalid NGO]

No problem SUMPRODUCT is more powerful:

=SUMPRODUCT(($B$3:$B$28=$C30)*(D$3:D$28)*($C$3:$C$28)/SUMPRODUCT(($B$3:$B$28=$C30)*($C$3:$C$28)))

Enter in D30, copy down and across

Regards,

 

[dramnath1981]

Thank you Khalid.

Its a new learning for me. Any similar shortcut for Data Filter as well?

 

[Khalid NGO]

Keep visiting keep learning, that's we do here all the day.

For Filter, use Advance Filter.

First make a copy of your column b, at any other column, say column C, and enter values which you want to include in your filter.

Than select your range: B2:B28
Go to Data > Advance
List Range = $B$2:$B$28
Criteria Range = $C$2:$C$24
Hit OK.

See the attached.

Regards,