Microsoft Excel - Excel macro - Asked By anu anu on 25-Mar-13 01:07 AM

 

hi ,

 

I have one excel data in base worksheet, I want some data calculate to other sheet, base file attach. in this file  some row color yellow  like month ,desc, total cost,Actual Branch,abc wise, in the file i have other sheet (summary) where i need calculation on month wise desc total cost and i need calculation abc wise total cost on month wise , also same total cost Actual Branch,

Months Item Description Desc T id T Date Qty UCost Total Cost Req ID  Current CDt CurrentReading Old Consu. Dt Old Mtr Reading Actual Branch Seri Model abc wise maneger Customer Name
Jan-12 wewqdss51313 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11 asas 15-Apr-11 asasa GGN 7854654 123 c xyz pogo
Jan-12 wewqdss51314 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11 asas 21-Nov-11 asasa GGN 7854654 123 c xyz pogo
Jan-12 wewqdss51315 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11 asas 18-Oct-11 asasa GGN 7854654 123 c xyz pogo
Jan-12 wewqdss51316 abc geeta 45451213 2-Jan-12 -1 1690.06 -1690.06 5645646 29-Dec-11 asas 5-Dec-11 asasa op 7854654 123 c wer pogo
Jan-12 wewqdss51317 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 27-Dec-11 asas 13-Dec-11 asasa op 7854654 123 c wer pogo

Pete Bradshaw replied to anu anu on 25-Mar-13 08:13 AM
Hi Usha,

If I understand you properly, are you looking to sum the Total Cost by either Month, Desc and Actual Branch column?

If so, then you could use the SumIf function.

Lets assume that Months column is column A on row 3, and you wanted to sum the total for the GGN branch. You would enter the formula =SUMIF(O3:O8,"GGN",I3:I8) and would give the answer -5070.

Here is how the formula works (copied from the excel helpfile)

SUMIF(range,criteria,sum_range)

Range    is the range of cells you want evaluated.

Criteria    is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range    are the actual cells to sum.

Hope this helps

Pete

anu anu replied to Pete Bradshaw on 26-Mar-13 05:54 AM
hi,

thanks it`s works but i have diffrent month data in this sheet ,i wants month wise data also, is any way out for this.

 like on  jan`13 geeta total 326
        feb `13 geeta total 526

thanks
usha
Pete Bradshaw replied to Pete Bradshaw on 26-Mar-13 11:33 AM
Hi Usha,

To use sumifs on dates, you need to put them in quotation marks like this =SUMIF(A3:A8,"01/01/2012",I3:I8)

The best way to do it would be to replace the middle part of the formula with a cell reference which will hold the date you're trying to sum.

Try this in cell A1 on your spreadsheet, enter the date you want to check e.g. 01/01/12. Then in your formula replace the "01/01/2012" with A1 like this =SUMIF(O3:O8,A1,I3:I8).

This way, you can change the contents of A1 and it will update the results without the need to alter your actual formula.

Let me know if you get stuck

Cheers

Pete
Harry Boughen replied to anu anu on 26-Mar-13 10:45 PM
Hello Usha,
Try
=SUMPRODUCT(($B$1:$B$6="geeta")*(MONTH($A$1:$A$6)=1),$C$1:$C$6)
Adjust the addresses to suit your layout for the columns that you want to use.  You can also put the variables that you want to compare (geeta and month number [1=Jan, 2= Feb]) into cells and refer to the cells in the formula.
Regards
Harry
anu anu replied to Harry Boughen on 28-Mar-13 01:05 AM
hi ,

thanks , but when i put sumproduct formuls some error is coming.

Months
Item Description Desc T id T Date Qty UCost Total Cost Req ID  Current CDt
Jan wewqdss51313 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11
Jan wewqdss51314 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11
Jan wewqdss51315 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 29-Dec-11
Jan wewqdss51316 abc geeta 45451213 2-Jan-12 -1 1690.06 -1690.06 5645646 29-Dec-11
Jan wewqdss51317 abc geeta 45451213 2-Jan-12 -1 1690.06 -1,690 5645646 27-Dec-11
Bitmap
#VALUE!







=SUMPRODUCT(($D$1:$D$6="geeta")*(MONTH($A$1:$A$6)=1),$H$1:$H$6)



Harry Boughen replied to anu anu on 28-Mar-13 01:29 AM
Hello Usha,
The values in columnA have to be proper date values.  For example todays date (28 March 2013) if you look at it as a number should be 41361.
If you want the months just to be plain text (eg Jan, Feb etc) then
=SUMPRODUCT(($D$1:$D$6="geeta")*(($A$1:$A$6)="Jan"),$H$1:$H$6)
Regards
Harry
anu anu replied to Harry Boughen on 28-Mar-13 02:27 AM
hi,

thank a lot it`s working

regards
usha
anu anu replied to Harry Boughen on 04-Apr-13 01:53 AM
hi harry

i am useing sumproduct formula ,and it`s working fine.but my sum figur in - and i want when i put sumproduct formula total shows without "-" sing.

thanks
anu
Harry Boughen replied to anu anu on 04-Apr-13 02:05 AM
Hello Usha,
Just put ABS( SUMPRODUCTetc).
Regards
Harry
anu anu replied to Harry Boughen on 04-Apr-13 02:25 AM
hi harry how to put this formula in my formula

my formula is

=SUMPRODUCT(($D$1:$D$6="geeta")*(($A$1:$A$6)="Jan"),$H$1:$H$6)

thank s
Usha
Harry Boughen replied to anu anu on 04-Apr-13 02:30 AM
Hi Usha,

=ABS(SUMPRODUCT(($D$1:$D$6="geeta")*(($A$1:$A$6)="Jan"),$H$1:$H$6))

Harry