Sum if

[SPLUCENA]

Hi All,

Can somebody please assist me with these issue of mine in excel.

I tried using sumif function but it is not working for me.

I have in the cells the following item. I want to add the value of gas only. SumIf function does not work for me. Please help.


Thanks,

splucena



ROW 1 ROW 2 TOTAL QTY
GAS 500
GAS 100
OTHER 800
GAS 200

 

[VoG]

Try

=SUMIF(A1:A10,"GAS",B1:B10)

 

[SPLUCENA]

Hi Vog,

Thank you very much. You saved my day.


Splucena

 

[SPLUCENA]

Hi Vog,

I forgot to include that I have two criteria in summing if. Kindly look at the example below.


ROW 1 ROW 2 ROW 3 TOTAL QTY
GAS 500 1-JAN-09
GAS 100 2-JAN-09
OTHER 800 1-JAN-09
GAS 200 1-JAN-09

I want the sumif function to specify for me the total date wise. I am trying to make two arrays with the date as object but it is giving me a zero value. Kindly help.



Thanks,

splucena

 

[VoG]

Try this

Excel Workbook
	A	B	C	D	E
1	GAS	500	01-Jan-09		700
2	GAS	100	02-Jan-09
3	OTHER	800	01-Jan-09
4	GAS	200	01-Jan-09
Sheet1

 

[SPLUCENA]

Hi Vog,

Thanks! The formula is just right to what I needed.

Bless you,

splucena

 

[SPLUCENA]

Hi Vog,

Is there a way I can have this formula written in VBA code? Because what I am currently doing right now is I am manually entering it cell by cell. I have around 31 columns and 20 cells in each column to fill manually and changing my datevalue from day 1 to day 31. Thank you for your assistance.


splucena

 

[VoG]

You can enter the dates of interest in cells and then use a formula like

<b>Sheet12</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:24px;" /><col style="width:75px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >GAS</td><td style="text-align:right; ">500</td><td style="text-align:right; ">01-Jan-09</td><td > </td><td style="text-align:right; ">01/01/2009</td><td style="text-align:right; ">700</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >GAS</td><td style="text-align:right; ">100</td><td style="text-align:right; ">02-Jan-09</td><td > </td><td style="text-align:right; ">02/01/2009</td><td style="text-align:right; ">100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >OTHER</td><td style="text-align:right; ">800</td><td style="text-align:right; ">01-Jan-09</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >GAS</td><td style="text-align:right; ">200</td><td style="text-align:right; ">01-Jan-09</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F1</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(A$1:A$10="GAS")</span>,--<span style=' color:008000; '>(C$1:C$10=E1)</span>,B$1:B$10)</td></tr><tr><td >F2</td><td >=SUMPRODUCT(--<span style=' color:008000; '>(A$1:A$10="GAS")</span>,--<span style=' color:008000; '>(C$1:C$10=E2)</span>,B$1:B$10)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[SPLUCENA]

hi Vog,

I have done that already. But is not what I really want. I think I will be able to explain what I wanted if I post my project. how do you copy paste the cells with the formulas when you reply? i am trying to do it but just can't be pasted.

splucena

 

[VoG]

Try using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php