SUMIFS with date written in formula

[trolle]

I have a worksheet with a annual daily dates horizontally.

Past this I want to have 12 columns that add the data from each date into a monthly overview; a poor man's pivot if you will.

I figure I will use the SUMIFS with a date range for the 12 months:

=+SUMIFS(W7:NW7,W6:NW6,"<=1/1/2018",W6:NW6,">=1/31/2018")

Question: How do I enter the date properly so that it will do the addition? My tests are not working.

If there is a better way than SUMIFS please let me know.

[samba_ravi]

Use "<="&date(2018,1,1) instead of "<=1/1/2018"
and use ">="&date(2018,1,31) instead of">=1/31/2018"

[Special-K]

Are your sure your condition is correct? <=1/1/2018 AND >=1/31/2018 ??? So any date EXCEPT JAN 2018 ???
Shouldnt this be >=1/1/2018 AND <=1/31/2018 ???

Decide what the condition should be and try

=SUMPRODUCT((W6:NW6<=1/1/2018")*(W6:NW6>1/31/2018")*(W7:NW7))

[samba_ravi]

I think it would be
">="&date(2018,1,1) and "<="&date(2018,1,31)

[kev_]

Please attach a workbook to your reply so that we can see exactly what you are trying to do etc
Ensure the workbook shows the "before" and "after" position


Attachment 527020

[xlnitwit]

Hi,

Rather than a "poor man's pivot", why not an actual pivot?

If you must use SUMIFS, I would suggest
=SUMIFS(W7:NW7,W6:NW6,">="&DATE(2018,1,1),W6:NW6,"<"&DATE(2018,2,1))
Note the change to the greater than and less than criteria order.

[trolle]

You are correct Special-K! I switched the signs...

Thanks nflsales...that worked