# CORRECT FORMULA TO WHEN USING SUMIF

#### JAZZWAKEFOREST

##### Board Regular
How do I get 3 different codes in a spreadsheet to give me a sum.
i.e.: looking for numbers that starts with 011,2 13, and 214.

Is this the correct formula?
=SUMIF(Rawdata!F2:F9000,11,13,14,Rawdata!H2:H9000)

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You wan them equal to 11, 13 and 14 or starting with 11, 13 and 14?

JAZZWAKEFOREST said:
How do I get 3 different codes in a spreadsheet to give me a sum.
i.e.: looking for numbers that starts with 011,2 13, and 214.

Is this the correct formula?
=SUMIF(Rawdata!F2:F9000,11,13,14,Rawdata!H2:H9000)

You say: "starts with 011,2 13, and 214" but the formula you attempted refers to 11, 13, and 14.

If codes are text, that is, not true numbers...

=SUM(SUMIF(Rawdata!F2:F9000,{"011","213","214"}&"*",Rawdata!H2:H9000))

If unsure about whether they are text or numbers... and it's "starts with: 11, 13, 14"... and F2:F9000 houses true numbers:

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT(Rawdata!F2:F9000,2)+0,{11,13,14},0)),Rawdata!H2:H9000)

Looking for number that start with the following numbers 011, 028, 039. the formula need to add them up and give me one total

JAZZWAKEFOREST said:
Looking for number that start with the following numbers 011, 028, 039. the formula need to add them up and give me one total

Didn't you try:

=SUM(SUMIF(Rawdata!F2:F9000,{"011","028","039"}&"*",Rawdata!H2:H9000))

which assumes the F-range to house text-numbers like numbers with leading 0's.

the cell is showing 0 and not the correct total

JAZZWAKEFOREST said:
the cell is showing 0 and not the correct total

There is a difference between:

'012345

and

12345

The first is a text-formatted number, the second a true number.

What result do you get with:

=ISNUMBER(RawData!F2)

Hi,

May I suggest also to test that H column has NOT text formatted numbers.

a really number.

Here is the what I'm trying to get: The cell should

Should be populated wih the sum of a combination of codes starting with "0780",0110","0140", and"0115"

Should it look like this?
=SUM(SUMIF(Rawdata!B2:B9000, {"0780","0110","0140", "0115},Rawdata!H2:H9000)

Did you try the SUMPRODUCT formula Aladin suggested above?

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT(Rawdata!F2:F9000,3)+0,{780,110,140,115},0)),Rawdata!H2:H9000)

Replies
12
Views
344
Replies
1
Views
468
Replies
2
Views
622
Replies
6
Views
576
Replies
2
Views
729

1,196,487
Messages
6,015,493
Members
441,898
Latest member
kofafa

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