Sumifs not working

[howard]

I have tried to use the folowings sumifs formula but cannot get it to work

I am trying to add the values in Col B pertaining to the transacation Type in Col G as well as to the account Number in Col A for the Criteria "Sales" i.e I am trying to sum the value in Col B, relating to "sales" for a particular number in Col A for eg account 12518


'=SUM(SUMIFS('[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$B:$B,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G),'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$A:$A,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G,"Sales")

See Source File Below

Excel Workbook
	A	B	C	D	E	F	G
1	12301	-2000					COS
2	12515	9708.2					COS
3	12518	96					Sales
4	12610	-88373					COS
5	13050	126119.7					COS
6	13110	38256.39					Sales
7	13300	700739.5					COS
8	13305	0					COS
9	13310	-4000					Sales
10	13350	0					COS
11	13410	-2925.5					Sales
12	13605	251.94					COS
13	13610	0					COS
Sheet1

 

[RoryA]

Why are you passing entire columns as criteria if you want the answer for a specific value?

 

[howard]

Hi Rory

I am trying to add up the values in Col B pertaining to sales in this instance that pertains to a particular account number for eg 12301-the criteria being sales

 

[RoryA]

What do you think the red bit does?

=SUM(SUMIFS('[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$B:$B,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G),'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$A:$A,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G,"Sales")

After we resolve that, the rest of the formula is a SUM of the result of the SUMIFS formula, plus the whole of column A, plus the word "Sales". I'm guessing that's not what you intended.

 

[howard]

The red bit takes into account the transaction type in Col G , being Sales, COS etc and if criteria is sales must be added

 

[RoryA]

Nope - it adds column B where Column G = Column G. I think what you mean is:

=SUMIFS('[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$B:$B,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$A:$A,12301,'[BR1 Vat Extraction .Oct 2012.Fin1.xlsm]Pivot Output'!$G:$G,"Sales")

 

[howard]

Thanks Rory, Works Perfectly

 

[howard]

Hi Rory

If I wanted to add an additional criteria , for eg "Internal Sales", being in Col G, how is this added to the sumifs?