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

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

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

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

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

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)

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

hi,

thank a lot it`s working

regards
usha

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

Hello Usha,
Just put ABS( SUMPRODUCTetc).
Regards
Harry

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

Hi Usha,

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

Harry