Sum only for one month in excel
 

Hello,
=SUMIF(Sheet1!D: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.

Sum only for one month in excel
 

What you are adding is in column S, so lets assume that One Month is from S15
to S24 then:
=SUMIF(Sheet1!D: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...

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"kallu kall" wrote:

Hello,
=SUMIF(Sheet1!D: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.

Sum only for one month in excel
 

You could do it this way:

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

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

On Dec 16, 7:45*pm, kallu kall wrote:
Hello,
=SUMIF(Sheet1!D: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.

Sum only for one month in excel
 

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:D$100=Sheet2!C25),--(MONTH(Sheet1!A$1:A$100)
=Sheet2!D25),Sheet2!S$1:S$100)

or like this:

=SUMPRODUCT((Sheet1!D$1:D$100=Sheet2!C25)*(MONTH(S heet1!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

On Dec 16, 8:01*pm, Pete_UK wrote:
You could do it this way:

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

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

On Dec 16, 7:45*pm, kallu kall wrote:



Hello,
=SUMIF(Sheet1!D: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.- Hide quoted text -

- Show quoted text -