SUMIF with Two Criterias - one of which is a Date Range

[thanhie]

Hello,

I am working on a report for work and need help with the following formula:

=SUMIF(A5:G8,AND(between 1-1-13 and 1-31-13,"soft cost"),G5:G8)

What I'm trying to say in my formula is "IF A5:G8 IS BETWEEN JANUARY 1st 2013 AND JANUARY 31st 2013 AND ALSO IF IT IS A SOFT COST THEN GIVE ME THE SUM OF THAT ROW"

I'm not sure if I make sense. Pretty much column A contains different dates and column C indicates whether a cost is a "hard cost" or a "soft cost". If the date is within the month of January AND if it is a soft cost, I need the dollar amount in column G summed-up (must meet both criteria - January and soft cost).

THANK YOU SO MUCH!!!

[benishiryo]

hi thanhie. you cannot lump all the ranges together. 1 range for 1 criteria. so:
=SUMIFS(G2:G8,A2:A8,">=1jan2013",A2:A8,"<=31jan2013",C2:C8,"soft cost")

or if I2 contains the date 1jan2013 then:
=SUMIFS(G2:G8,A2:A8,">="&I2,A2:A8,"<="&EOMONTH(I2,0),C2:C8,"soft cost")

[thanhie]

Perfect! Thanks it works!

I have a question though just so I can gain some more excel knowledge, in the second formula you gave me, what does I2 mean? Or better yet, what does this section say: ">="&I2,A2:A8,"<="&EOMONTH(I2,0) ? thanks!

[benishiryo]

glad to help.

as mentioned, i'm assuming I2 has the date 1jan2013. if you understand the 1st formula, this one is merely referring to a cell instead of typing manually:
A2:A8,">=1jan2013",A2:A8,"<=31jan2013"

the above means A2:A8 must be greater or equals to 1Jan2013 AND A2:A8 must be less than or equals to 31Jan2013

so referring to a cell which has 1jan2013, it means the same for the 1st part. the 2nd part is making use of the EOMONTH formula to get the End Of Month date for I2. by putting 0 in the 2nd argument of EOMONTH, i'm taking the end date of I2 (so it's 31 Jan 2013). if i use:
EOMONTH(I2,1)
i'm taking the end date 1 month later, and that's 28 Feb 2013. if i use:
EOMONTH(I2,-1)
i'm taking the end date 1 month before, and that's 31 Dec 2012

[thanhie]

Hi Benishiryo,

Can you please take a look at my spreadsheet and let me know what i am doing wrong? i can't seem to apply your second formula to my spreadsheet correctly. The formula you gave me is =SUMIFS(G2:G8,A2:A8,">="&I2,A2:A8,"<="&EOMONTH(I2,0),C2:C8,"soft cost")

Thank you so much!