Sum only for one month in excel

[kallu kall]

Hello,
=SUMIF(Sheet1!D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 I have data for one year.
How cen I get this data only for one month from sheet1 to sheet2.

 

[Michael]

What you are adding is in column S, so lets assume that One Month is from S15
to S24 then:
=SUMIF(Sheet1!D,Sheet2!C5,Sheet1!S15:S24)

C5 is your criteria, so if you probably have change your C5 to something
that will indicate how to identify the month in Column D...

 

[Pete_UK]

You could do it this way:

=SUMPRODUCT(--(MONTH(Sheet1!D$1$100)=Sheet2!C5),Sheet2!S$1:S$100)

where C5 contains a month number (1 to 12). If you have more than one
year's worth of data then you will need another condition to catch the
year.

Note that with SUMPRODUCT you can't use full-column references (unless
you have XL2007).

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I think I have misunderstood what you wanted.You already have a
condition, and now you want to apply a second one to limit the sum to
one month. You can do that this way:

=SUMPRODUCT(--(Sheet1!D$1$100=Sheet2!C25),--(MONTH(Sheet1!A$1:A$100)
=Sheet2!D25),Sheet2!S$1:S$100)

or like this:

=SUMPRODUCT((Sheet1!D$1$100=Sheet2!C25)*(MONTH(Sheet1!A$1:A$100)
=Sheet2!D25),Sheet2!S$1:S$100)

Both of these assume that your dates are in column A, and that you
enter a month number in D25 of Sheet2.

Adjust the ranges to suit your data, subject to my earlier caveat
about full-column references.

Hope this helps.

Pete