SumProduct for cells based on date format

[tjc154]

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

 

[etaf]

can you use SUMIFS

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

 

[jai9]

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]

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]

ADVERTISEMENT

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]

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]

ADVERTISEMENT

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]

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

 

[Aladin Akyurek]

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

=SUMPRODUCT(
   --($I$2:$I$693="TXA"),
   --($R$2:$R$693>="4/8/2013"+0),
   --($R$2:$R$693<="4/15/2013"+0)

Or...

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

 

[tjc154]

Etaf-

Thanks the revised code worked.

Tom