# Sum Product with Criteria

#### RPopowich

##### Board Regular
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)

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??

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

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)

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)

Replies
1
Views
131
Replies
0
Views
257
Replies
0
Views
124
Replies
0
Views
85
Replies
8
Views
261

1,196,131
Messages
6,013,631
Members
441,777
Latest member

### 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