CODEWITHSUNDEEP
excelexcel-formulaexcel-2011
leifericf
leifericf

Reputation: 2372

Wildcard condition with * for SUMIFS formula on a column with number values

I have a huge excel sheet that looks like this:

╔══════╦══════╦═════╗
║  A   ║  B   ║  C  ║
╠══════╬══════╬═════╣
║ Jack ║ 2001 ║ 1,5 ║
║ Jack ║ 2002 ║ 2,0 ║
║ Jack ║ 2003 ║ 1,0 ║
║ Jack ║ 3001 ║ 3,5 ║
║ Jack ║ 3002 ║ 4,0 ║
║ Jack ║ 3003 ║ 1,0 ║
║ Jill ║ 2001 ║ 3,0 ║
║ Jill ║ 2002 ║ 5,0 ║
║ Jill ║ 2003 ║ 2,0 ║
║ Jill ║ 3001 ║ 0,5 ║
║ Jill ║ 3002 ║ 6,0 ║
║ Jill ║ 3003 ║ 2,5 ║
╚══════╩══════╩═════╝

Column B contains many different numbers, but they all begin with digits 2, 3 or 8. The numbers in column B are always be 4 digits long; I'm only interested in checking the first digit.

I need to add together the values of column C, where the first digit of the corresponding cell in column B is either 2*, 3* or 8*. What I need is to create a formula that does this (Ruby-esque pseudocode):

sum = 0
spreadsheet_rows.each do |row|
  if row.a == "Jack" and row.b == "2*" # Note the second wildcard condition.
    sum += row.c
  end
end

puts sum # Should print 4,5 in this example.

I'm trying to use the following formula in Excel to accomplish this:

=SUMIFS($C:$C; $A:$A; "Jack"; $B:$B; "=2*")

I know that Excel does not support wildcard conditions for numbers, however, I have formatted column B as type "Text" in Excel, so I thought it would be treated as such, but it appears that it is still treated as an int.

Is there a different way of applying a wildcard condition in =SUMIFS for number values in Excel? Perhaps there's a way to somehow "cast" the integers to strings in the formula? I haven't found a way to do it (yet).

I'm using Excel for Mac 2011.

Upvotes: 2

Views: 12286

Answers (3)

Rauf Waheed
Rauf Waheed

Reputation: 1

{=SUM((A1:A12=F2)*(LEFT(B1:B12)=""&F3)*C1:C12)}

No need for an arrays as shown above just type following formula which gives equal results to above Arrays formula 

=SUMPRODUCT((A1:A12=F2)*(LEFT(B1:B12)=F3&"")*C1:C12)

Remember difference [Arrays=""&F3] vs [SUMPRODUCT= F3&""] I shall be bring to your kind I am just very very very happy to see your work and the way you authoritatively asked question as as

  "I know that Excel does not support wildcard conditions for numbers, however, I have 
   formatted column B as type "Text" in Excel, so I thought it would be treated as 
   such, but it appears that it is still treated as an int."

SUMPRODUCT only not work when we have to get output in TEXT Hoping your verification about all above and kindly highlight at prominent place

Upvotes: 0

MP24
MP24

Reputation: 3200

I'd go for the less readable, but more powerful SUMPRODUCT:

=SUMPRODUCT(($A:$A="Jack") * (LEFT($B:$B;1)="2") * ($C:$C))

which will generate boolean arrays for each of the conditions (first and second brace part) which it will multiply with the third one (your numbers).

EDIT: As noted in comments, #VALUE errors can appear if any value in column C cannot be converted to a number. To avoid that, you could use the syntax suggested by barry houdini

=SUMPRODUCT(($A:$A="Jack") * (LEFT($B:$B;1)="2"); $C:$C)

and let SUMPRODUCT skip over non-numbers.

Upvotes: 3

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

This works for me:

=SUM((A1:A12=F2)*(LEFT(B1:B12)=""&F3)*C1:C12)

entered as an array formula with CtrlShiftEnter

You ask how to cast numbers to strings; concatenating an empty string and a number ""&F3 is one way to do that.

enter image description here

Upvotes: 1

Related Questions