# SumProduct for cells based on date format

#### tjc154

##### Active Member
I'm using the following formula to sum all cells that have TXA in column I and a value in column R.

=SUMPRODUCT(--(\$I\$2:\$I\$693="TXA"),--ISNUMBER(\$R\$2:\$R\$693))

I would like to modify the second half of the formula so it counts all cells that match a specific date range using the following date format.

Ex: 4/8/2013 through 4/15/2013

Is this possible. If so how would I revise my formula?

Thank you

Tom

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### etaf

##### Well-known Member
can you use SUMIFS

=sumifs(\$I\$2:\$I\$693, "TXA" , Range_date in , ">=4/8/2013" ,Range_date in, "<= 4/15/2013")

#### jai9

##### Active Member
Hi,

Try,

=SUMPRODUCT((\$I\$2:\$I\$693="TXA")*(\$R\$2:\$R\$693>N1)*(\$R\$2:\$R\$693<N2))

where N1=4/8/2012 and N2=4/15/2013.

Jai

#### tjc154

##### Active Member
I tried both formulas.

Etaf- Is something missing to reference the column and range for the dates?

In the second formula, I replaced N1 and N2 with the dates as shown below. I inputed a few dates, but the value remains "0" it should have shown 5?

=SUMPRODUCT((\$I\$2:\$I\$693="NPC")*(\$R\$2:\$R\$693>4/8/2013)*(\$R\$2:\$R\$693<4/15/2013))

Thank you

#### jai9

##### Active Member
Hi,

Try

=SUMPRODUCT((\$I\$2:\$I\$693="NPC")*(\$R\$2:\$R\$693>Date(2013,4,8))*(\$R\$2:\$R\$693<Date(2013,4,15)))

Jai

#### etaf

##### Well-known Member
was not sure where the dates where located ..

=SUMIFS(
\$I\$2:\$I\$693,"NPC", \$R\$2:\$R\$693, ">4/8/2013", \$R\$2:\$R\$693, "<4/15/2013")
assuming you dont want to include the dates otherwise , you need to include = with the < and > signs

#### tjc154

##### Active Member
Etaf,

I tried the revised formula, but its not even giving me a value in the cell. It just shows the formula. I tried it in different cells. Not sure why its not working??

Thanks,

Tom

#### etaf

##### Well-known Member
sorry my bad - we need a range we are actually summing

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

=SUMIFS(Range_of_Cells_to_SUM, \$I\$2:\$I\$693,"NPC", \$R\$2:\$R\$693, ">4/8/2013", \$R\$2:\$R\$693, "<4/15/2013")

I think, i have mislead you as you are counting and not summing

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

=COUNTIFS(
\$I\$2:\$I\$693,"NPC", \$R\$2:\$R\$693, ">4/8/2013", \$R\$2:\$R\$693, "<4/15/2013")

http://office.microsoft.com/en-gb/excel-help/countifs-function-HA010047494.aspx

Apologise for that, should have read a little closer your 1st post - sorry to have wasted some of your time

Last edited:

##### MrExcel MVP
i'm using the following formula to sum all cells that have txa in column i and a value in column r.

=sumproduct(--(\$i\$2:\$i\$693="txa"),--isnumber(\$r\$2:\$r\$693))

i would like to modify the second half of the formula so it counts all cells that match a specific date range using the following date format.

Ex: 4/8/2013 through 4/15/2013

is this possible. If so how would i revise my formula?

Thank you

tom

Try:

Either...
Rich (BB code):
``````=SUMPRODUCT(
--(\$I\$2:\$I\$693="TXA"),
--(\$R\$2:\$R\$693>="4/8/2013"+0),
--(\$R\$2:\$R\$693<="4/15/2013"+0)``````
Or...
Rich (BB code):
``````=COUNTIFS(
\$I\$2:\$I\$693,"TXA",
\$R\$2:\$R\$693,">=4/8/2013",
\$R\$2:\$R\$693,"<="4/15/2013")``````

#### tjc154

##### Active Member
Etaf-

Thanks the revised code worked.

Tom

Replies
4
Views
108
Replies
7
Views
155
Replies
3
Views
137
Replies
1
Views
93
Replies
3
Views
124

1,195,994
Messages
6,012,748
Members
441,724
Latest member
Aalbid

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