Sumproduct Help Needed

mazher

Active Member
Joined
Nov 26, 2003
Messages
355
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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)
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Got it working , there was some typing error in the formula.

Thanks for the help.

Regards

Mazher
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top