# Sumproduct Help Needed

#### mazher

##### Active Member
Hi Excel Gurus,

I have the following formulas in the cells,

K3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(K\$2=\$G\$2:\$G\$3000),\$D\$2:\$D\$3000)
L3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(L\$2=\$G\$2:\$G\$3000),\$D\$2:\$D\$3000)
M3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(M\$2=\$G\$2:\$G\$3000),\$D\$2:\$D\$3000)

Where
K2=4
L2=5
M2=6

Can some one help me so that the formula in
K3 gives me all values<=4
L3 gives me all values >4 and <=5
M3 gives me values >5 and <=6

Help will be extremely appreciated.

Regards

Mazher

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Yard

##### Well-known Member
DO you mean

K3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000<=K\$2),\$D\$2:\$D\$3000)
L3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000>K\$2),--(\$G\$2:\$G\$3000<=L\$2),\$D\$2:\$D\$3000)
M3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000>L\$2),--(\$G\$2:\$G\$3000<=M\$2),\$D\$2:\$D\$3000)

#### Weaver

##### Well-known Member
or

K3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000<=K\$2),\$D\$2:\$D\$3000)
L3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000<=L\$2),\$D\$2:\$D\$3000)-K3
M3=SUMPRODUCT(--(J3=\$F\$2:\$F\$3000),--(\$G\$2:\$G\$3000<=M\$2),\$D\$2:\$D\$3000) -Sum(K3:M3)

#### mazher

##### Active Member
Thanks this worked

I cells

J3=39
J4=42
J5=48
J6=54
J7=60
J8=66
J9=72

I modified the formula as but is not giving me the desired results

K3=SUMPRODUCT(--(\$F\$2:\$F\$3000<=\$J\$3),--(\$G\$2:\$G\$3000<=K\$2),\$D\$2:\$D\$3000)

In K3 I want add the only that values that <= 39
Similarly

K4= greater than 39 less than and equal to 42
K5= greater than 42 less than and equal to 48
K6= greater than 48 less than and equal to 54
K7= greater than 54 less than and equal to 60
K8= greater than 60 less than and equal to 66
K9= greater than 66 less than and equal to 72

Hope I am able to exprres my problem.

Regards

Mazher

#### Yard

##### Well-known Member
Don't understand what isn't working. Your formula is giving you:

the sum of all cells in D2:D3000 where

the corresponding value (i.e. row) in column F is <=J3

and where

the corresponding value (i.e. row) in column G is <=K2

#### mazher

##### Active Member
Got it working , there was some typing error in the formula.

Thanks for the help.

Regards

Mazher

Replies
10
Views
105
Replies
9
Views
188
Replies
8
Views
173
Replies
5
Views
160
Replies
2
Views
71

1,191,705
Messages
5,988,185
Members
440,136
Latest member
dandanfielding

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back