CORRECT FORMULA TO WHEN USING SUMIF

JAZZWAKEFOREST

Board Regular
Joined
Dec 1, 2005
Messages
92
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
You wan them equal to 11, 13 and 14 or starting with 11, 13 and 14?
 
Upvote 0
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)
 
Upvote 0
Looking for number that start with the following numbers 011, 028, 039. the formula need to add them up and give me one total
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top