# Sumifs not working

#### howard

##### Well-known Member
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
ABCDEFG
112301-2000COS
2125159708.2COS
31251896Sales
412610-88373COS
513050126119.7COS
61311038256.39Sales
713300700739.5COS
8133050COS
913310-4000Sales
10133500COS
1113410-2925.5Sales
1213605251.94COS
13136100COS
Sheet1

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Why are you passing entire columns as criteria if you want the answer for a specific value?

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

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.

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

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")

Thanks Rory, Works Perfectly

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?

Replies
1
Views
559
Replies
6
Views
248
Replies
1
Views
420
Replies
2
Views
270
Replies
3
Views
878

1,219,792
Messages
6,150,291
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back