Sumif

[Dorothych]

I have country(Col A) , product(Col B) in Sheet 1, and I need to extract information from Sheet 2 which has the same format as Sheet 1. On Sheet 1 Col C, I need to know a total quantity purchase for Country and by Product. On sheet 2, it looks something like :

Brazil , TV , 5
India, Oven , 8
Brazil , Tv , 8
Brazil , Oven , 9

Say, on Sheet 1 Row 3

Brazil, Tv , QTY???
Can I use Sum if (with 2 criteria - Col A & B) result = sum of qty.
I hope this is not too confusing

Dorothy

 

[Dorothychin]

Can anyone pls help?

 

[plettieri]

It looks like you might want to try a pivot table using two multiple consolidation ranges. The pivot table wizard will walk you through the steps.... DATA | Pivot table


Hope this helps...

pll

 

[zzydhf]

Try this:
{=SUM(IF(Sheet2!$A$2:$A$16=A2,IF(Sheet2!$B$2:$B$16=B2,Sheet2!$C$2:$C$16)))}

Do not type the {}. when entering the formula, do not hit enter or tab or arrow, you must hit Ctrl+Shift+Enter
HTH

 

[Yogi Anand]

Hi Dorothy:

Considering the data on the same sheet, I have used the SUMPRODUCT formula to calculate Brazil TV combination sum -- see the worksheet simulation ...<SPAN id='ForSubmit'>

y021008a.xls
	A	B	C	D	E	F	G
1	Brazil	TV	5				Brazil
2	India	Oven	8		13		TV
3	Brazil	TV	8
4	Brazil	Oven	9
Sheet1

</SPAN>

or if your table is in Sheet2, then you can use the following formula in sheet1

'=SUMPRODUCT((Sheet2!A1:A4=G1)*(Sheet2!B1:B4=G2)*(Sheet2!C1:C4))


Regards!

Yogi
This message was edited by Yogi Anand on 2002-10-08 23:17

 

[Fryer Tuck]

I took a different tact, with one extra step. In column D of Sheet2 (you could put it in column A and hide it if you want), put the concatenation of A and B (just use the formula "= A2 & B2")

Then in column C of sheet 1, put the formula

=SUMIF(Sheet2!D$1:D$4,A2 & B2,Sheet2!C$1:C$4)

You can copy this down through all the matches.

F.T.

 

[ElectricSkywalker]

Hi guys,

I was just playing around with a the formulas that you all provided, and I really love them.

Just for anyone else out there who might be experimenting....

I discovered with "zzydhf's" formula that uses the Array function {}, that if I didn't ensure the ranges were exactly the same, then I would get an error.

Such as:

INCORRECT VERSION:
{=sum(if(A2:16=A2, if(B2:B16=B2, C2:C32)))}

CORRECT VERSION:
{=sum(if(A2:16=A2, if(B2:B16=B2, C2:C16)))}

I learned this the hard way - so keep an eye on your Ranges!

Cheers,
Greg

P.S. These are wicked formulas :0)