Sum of data !!!

[G21]

HI ,
Im having a requirement to add values in a column according to the data combination in other two columns.

eg-
X A 10
X C 12
X B 11
y A 89
y A 23

in above example , i want to get a sum of values of all X excluding X-B combination. ( So X-A & X-C combinations will get sum up)

Thanks in advance,
G

 

[sulakvea]

hi, try this:

=SUMPRODUCT(--(A1:A5="X"),--(B1:B5<>"B"),C1:C5)

 

[T. Valko]

HI ,
Im having a requirement to add values in a column according to the data combination in other two columns.

eg-
X A 10
X C 12
X B 11
y A 89
y A 23

in above example , i want to get a sum of values of all X excluding X-B combination. ( So X-A & X-C combinations will get sum up)

Thanks in advance,
G

This one will work in any version of Excel:

=SUMPRODUCT(--(A2:A6="X"),--(B2:B6<>"B"),C2:C6)

This one will work in Excel 2007 and later:

=SUMIFS(C2:C6,A2:A6,"X",B2:B6,"<>B")

 

[snoopyhr]

or maybe this :

Excel Workbook
	A	B	C	D	E
1	X A	10		Sum of column not equal to X B	134
2	X C	12
3	X B	11
4	y A	89
5	y A	23
Sheet5

 

[G21]

HI All,
Thanks for the quick response. All solutions are working.snoopyhr , your solution count all X & Y together. I need one a time.

Now need one more thing for the same. Need this equation as VBA statement to include to a macro with the specific sheet name.

RGDS,
G

 

[G21]

HI,
i need to convert the below formula in to VBA statement to run with Excel Macro. pls advise...

=SUMIFS(IP_SPACE!I5:I100,IP_SPACE!B5:B100,"x:",IP_SPACE!F5:F100,"<>BLANK")

Thanks & RGDS,
G

 

[G21]

HI All,
Got it resolved. Thanks for the support....worked the code as..

ActiveCell.FormulaR1C1 = _
        "=SUMIFS(IP_SPACE!R[2]C[3]:R[97]C[3],IP_SPACE!R[2]C[-4]:R[97]C[-4],""x:"",IP_SPACE!R[2]C:R[97]C,""<>BLANK"")"

Thanks & RGDS,
G