=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building 14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M

You need to use SUMPRODUCT for multiple criteria
=SUMPRODUCT(--(D3:D3001="014"), --(MONTH(I4:I10+=1), G4:G10)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you have Excel 2007 you can use SUMIFS
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chickalina" wrote in message
...
=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building 14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M

Hi,

Try something like this

=SUMPRODUCT(--(D3:D10="014"),--(I4:I10=DATE(2009,1,1)),--(I4:I10<=DATE(2009,1,31)),G4:G10)

But note your logic is different from that which I am using above. For
example your first range D3:D3001 does not match your other criteria ranges.
Your formula misses Jan 31.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"chickalina" wrote:

=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building 14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M

I'm a bit confused as to what you are trying to do. You seem to be testing
column I dates in rows 4 to 10, but column D values in rows 3 to 3001.

Your 2nd and 3rd terms will subtract those values beween G4 and G10 which
correspond to column I dates from 1 to 30 January 2009 (not 31st, because
you've tested for *less than* 31st), but your first term isn't valid because
the ranges are of different lengths (to 3001 in D, but to row 5000 in G).

If you correct the array lengths in the first part, that will add those
values in G3 to G3001 (or to G5000) for which the corresponding column D
value is "014". After that your 2nd and 3rd terms will subtract the
selected (1 to 30 January 2009) values within your shorter range (rows 4 to
10).

If all your ranges were the same length and you were looking at rows where
columns D and I both met the specified criteria, you would use SUMPRODUCT.
--
David Biddulph

chickalina wrote:
=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for
building 14 (located in column D) from January 1 to January 31
(Located in column I). Column G is where the monetary values are....

Thanks for any help!
M

Hi,

In 2007

=SUMIFS(G3:G10,D3:D10,"014",I3:I10,"="&DATE(2009, 1,1),I3:I10,"<="&DATE(2009,1,31))

In my prevous post I failed to notice that you are running some ranges from
row 3 and others from row 4. All ranges must be of equal size for these
formulas, so you should correct my previous suggestion accordingly.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"chickalina" wrote:

=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building 14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M

A typo!
=SUMPRODUCT(--(D3:D3001="014"), --(MONTH(I4:I10)=1), G4:G10)


--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
You need to use SUMPRODUCT for multiple criteria
=SUMPRODUCT(--(D3:D3001="014"), --(MONTH(I4:I10+=1), G4:G10)
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

If you have Excel 2007 you can use SUMIFS
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chickalina" wrote in message
...
=SUMIF(D3:D3001,"014",
G3:G5000)+SUMIF(I4:I10,"<"&DATE(2009,1,1),G4:G10)-SUMIF(I4:I10,"<"&DATE(2009,1,31),G4:G10)

what's wrong with this code? I'm looking to sum everything for building
14
(located in column D) from January 1 to January 31 (Located in column I).
Column G is where the monetary values are....

Thanks for any help!
M