SUMIFS Between dates that are greater than zero

[stevencjones]

Hi All

I am running office 2008 for mac.

I want to sum values greater than zero between two dates. I can do both functions separately.

Early date G16
Late Date G17
Date Range A20:A159
Sum Range M20:M159

The formula I have been using for separately between two dates is

=SUMIF($A$20:$A$159,"=>"&$G$16,M20:M159)-SUMIF($A20:$A159,"=>"&$G$17,M20:M159)

this is an old forumla formulai coped from daily dose of excel blog.

the obvious in 2008 i see as a SUMIFS function. I have tried this

=SUMIFS(M20:M200,M20:M200,">0")

this works fine for greater than zero

however i cannot get it to work in conjunction with specific dates (G16 and 17) in the date range A20:A159. here is what i tried.

=SUMIFS(M20:M159,M20:M159,">0",A20:A159,$G$16&">=",A20:A159,$G$17&"<=")

I have looked at help and searched for an answer but cannot find one.

any help appreciated

thanks for your time

Ste

 

[stevencjones]

Solved

I posted the same question in another forum as well

The answer is

doesnt it have to look like this ? with the "<="& before the cell ref?

=SUMIFS(M20:M159,M20:M159,">0",A20:A159,">="&$G$16,A20:A159,"<="&$G$17)

This works fine now

Thanks for your time

Ste