SUMIFS formula with data table in 2010

[jblack]

Hi,


I have setup a data table using Excel 2010. One of my columns is the Due Date for receivables. Another column is the amount due ($Amount). Outside the data table I have setup 2 columns for my date ranges – they are titled StartDate and EndDate. All three of the date columns have the same date format.


I am trying to write a formula that will give me the sum total for my $Amount column for those dates in the Due Date column that meet the criteria of the date range that I have selected as the StartDate and EndDate.


I have come up with the following formula:


=SUMIFS(Table1[$ Amount],Table1[Due Date],">=P2",Table1[Due Date],"<=Q2")


which results in 0 instead of the correct summing of values in the $Amount column for those Due Dates that fall in the date range selected. In the above formula, P2 is the start date for my date range and Q2 is the end date for my date range. I’m scratching my head on this one.

 

[Debraj]

Hi Jblack!


Can you please try the below for me..


=SUMIFS(Table1[$ Amount],Table1[Due Date],">="&P2,Table1[Due Date],"<="&Q2)


Regards,

Deb

 

[jblack]

Deb,


That worked like a charm. Could you explain why doing that did work?


Thanks

 

[Debraj]

Hi Jblack,


* ">=" is a Opearator.

* "P2" is a Reference of cell.


so, if you put both in double quote, both will work as a text, and if we put both in unquote, it will not able to check it as a operator or Reference..


We we need to put Operator in between Double quote and reference outside the quote..

and a "&" sign to concatenate both..


Regards,

Deb

 

[bobhc]

Good evening Debraj Roy

It is because yourself,SirJb7, Luke M and others take the time to explain what the formula/function is doing and how it works that others learn

 

[jblack]

Thanks for the explanation Deb. I appreciate your help.