# Problem using SUMIFS

#### jlonn

##### New Member
I have constructed an Excel spreadsheet containing the table in Example 1 on Microsoft's support page on the SUMIFS function:

SUMIFS function - Excel - Office.com

See below for the entire table. In my spreadsheet I get #NAME? errors in the cells where the SUMIFS functions are. What might the problem be? The formulas in this example should work, shouldn't they?

 Quantity Sold Product Salesperson 5 Apples 1 4 Apples 2 15 Artichokes 1 3 Artichokes 2 22 Bananas 1 12 Bananas 2 10 Carrots 1 33 Carrots 2 Formula Description Result =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1) Adds the total number of products sold that begin with "A" and that were sold by Salesperson 1. 20 =SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1) Adds the total number of products (not including Bananas) sold by Salesperson 1. 30

<TBODY>
</TBODY>

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Which version of Excel are you using?
The SUMIFS function was not available until XL2007+

Which version of Excel are you using?
The SUMIFS function was not available until XL2007+

Excel 2003. That explains it. Thanks!

Cool, post the sumifs formula and we can work out an equivelent that will work in XL2003

In Excel 2003, you can use SUMPRODUCT if you have multiple conditions.

So the equivalent of the two SUMIFS formulas you posted in your first post would look like this:
Code:
``=SUMPRODUCT(--(C2:C9=1),--(LEFT(B2:B9,1)="A"),--(A2:A9))``
Code:
``=SUMPRODUCT(--(C2:C9=1),--(B2:B9<>"Bananas"),--(A2:A9))``

By the way, is there a good way (in the example above) to replace SUMIFS with multiple SUMIFs or some similar construct?

Replies
3
Views
388
Replies
1
Views
134
Replies
9
Views
203
Replies
3
Views
208
Replies
4
Views
665

1,196,254
Messages
6,014,279
Members
441,811
Latest member
Peco73267326

### 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