Sum Product with Criteria

RPopowich

Board Regular
Joined
Jan 6, 2005
Messages
89
Can you help me with a formula to calculate the following:

1 2 3 4 5 6 7
32 32 32 27 27 27 27
32 32 32 27 27 27 27
These are weeks that I want to convert into a monthly number. The weeks will roll the most current week every time I pull the report. I'm looking for a formula that would add the row of numbers that match the week numbers i input.

For example.

Jan would be 32+32+32+27=123

Is there a formula that would say if Week range is 1-4 sum so that no matter which order my weeks are in Jan would always be a sum of weeks 1-4?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try:

=SUMPRODUCT(--($A$1:$G$1<5),$A$2:$G$2)

Change the ranges as you like, but both arrays have to be over the same number or rows/columns.

EDIT:
This will also work:
=SUMIF($A$1:$G$1,"<5",$A$2:$G$2)
 
Upvote 0
Thank you. Just one more question. How do I change the range to show weeks 5 thru 8? greater than 4 but less than 9??
 
Upvote 0
The logic is add all those above 4 and then subtract all those above 8:

=SUMIF($A$1:$g$1,">4",$A$2:$g$2)-SUMIF($A$1:$g$1,">8",$A$2:$g$2)
 
Upvote 0
The logic is add all those above 4 and then subtract all those above 8:

=SUMIF($A$1:$g$1,">4",$A$2:$g$2)-SUMIF($A$1:$g$1,">8",$A$2:$g$2)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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