Help Required on Sumproduct with multiple condition

[Niraj Baraili]

Hi Expert,

Again need your help on this.

I need to take sumproduct of col D data based on the checks given on col I1:I8.

I tried the formula SUMPRODUCT((B:B=I1)*(B:B=I2)*(B:B=I3)*(B:B=I4)*(B:B=I5)*(B:B=I6)*(B:B=I7)*(B:B=I8)*(D:D)), but somehow it's working. Test file has been attached for your reference.

 

[Niraj Baraili]

Just missed the attachment.

______________________________________________________________

Mod edit : thread moved to appropriate forum !
​

 

[Villalobos]

Hi,

Is this formula what you are looking for?

=SUM(SUMIF(B:B,I1:I8,D:D)) entered with CSE

 

[Niraj Baraili]

Hi Villalobos,

Thanks for your reply.

I think i havn't explained it correctly what i was looking.

I need to take sumproduct of col D as below:

SUMPRODUCT(D:D,C:C)/SUM(C:C) with col b containing these numbers.
3411
3412
3413
3414
3415
3416
3417
3418

 

[Niraj Baraili]

Tried this below formula...still it's not working

SUMPRODUCT(((B2:B100=J1)+(B2:B100=J2)+(B2:B100=J3)+(B2:B100=J4)+(B2:B100=J5)+(B2:B100=J6)+(B2:B100=J7)+(B2:B100=J8))*(D2:D100)*(C2:C100))

 

[Hui]

The sum of Columns C & D where B >= I1 and B <=I8 is
=SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295)*(C2:C295))

If Column C is required
The sum of Column D where B >= I1 and B <=I8 is
=SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295))

If your having trouble explaining it highlight which cells should be included in a simple example

 

[Hui]

Looking at your proposal SUMPRODUCT(D:D,C:C)/SUM(C:C) above
That looks like you want the weighted average value of Column D based on weighting of Column C subject to the Criteria matching Column B

If that is the case:
=SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295)*(C2:C295))/SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(C2:C295))

 

[Hui]

Your formula below goes to Row 100, but the data goes to Row 295?

Tried this below formula...still it's not working

SUMPRODUCT(((B2:B100=J1)+(B2:B100=J2)+(B2:B100=J3)+(B2:B100=J4)+(B2:B100=J5)+(B2:B100=J6)+(B2:B100=J7)+(B2:B100=J8))*(D2:D100)*(C2:C100))

 

[Niraj Baraili]

Hi Hui,

Thanks for your reply. I just missed the range.
Also, i have a query on below formula:

=SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295)*(C2:C295))/SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(C2:C295))

B2:B295, D2:D295, C2:C295 are the data range. Now instead of this range i am used B:B, D:D, C:C, i got value error. Note, B1, C1 and D1 contains column header.

 

[Hui]

Firstly, The error is there because you can't multiply text by text (the headers rows)

But more important is that if you look at the structure of the formula:
=SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295)*(C2:C295))/SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(C2:C295))

You will see that Excel has to do 294 multiplications x 4 for the first Sumproduct and a further 294 multiplications x 3 for the first Sumproduct
ie: 1176 + 882 Calculations and then add up the 1176 and 882 values and do a division

If you extend the ranges to whole columns:
=SUMPRODUCT((B:B>=I1)*(B:B<=I8)*(D:D)*(C:C))/SUMPRODUCT((B:B>=I1)*(B:B<=I8)*(C:C))
You will see that Excel has to do 10487576 multiplications x 4 for the first Sumproduct and a further 1048576 multiplications x 3 for the first Sumproduct
ie: 4,194,304 + 3,145,728 Calculations and then add up the 4,194,304 and 3,145,728 values and do a division.

This is approximately 3566 times more calculations than the original range and will seriously slow down your files recalculation times

This is why spreadsheets get bad names because you are asking too much of it

 

[Hui]

Does =SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(D2:D295)*(C2:C295))/SUMPRODUCT((B2:B295>=I1)*(B2:B295<=I8)*(C2:C295))

Work?

 

[NARAYANK991]

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=SUM(($B$2:$B$295=TRANSPOSE($I$1:$I$8))*($C$2:$C$295)*($D$2:$D$295))/SUM(($B$2:$B$295=TRANSPOSE($I$1:$I$8))*($C$2:$C$295))

The advantage of this is that the items in the range I1:I8 can be non-contiguous , and need not be in sorted order.

Narayan

 

[Niraj Baraili]

Thank you very much.

 

[Somendra Misra]

Hi Niraj,

Just another option, try this one out also.

=SUMPRODUCT(COUNTIF($I$1:$I$8,$B$2:$B$295),$C$2:$C$295,$D$2:$D$295)/SUMPRODUCT(COUNTIF($I$1:$I$8,$B$2:$B$295),$C$2:$C$295)

Regards,