2

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.

leifericf
  • 2,324
  • 3
  • 26
  • 37
  • 3
    +1 for ╔ ASCII table ═╣ – Jean-François Corbett Sep 01 '14 at 08:53
  • Are they all 4 digit numbers? - if so I think paxdiablo's (deleted) suggestion would work best - all the `"IFS"` family of functions effectively convert non-numeric values to numeric where possible - which is why `=COUNTIF(A:A,2)` counts both numeric 2s and text formatted "2"s – barry houdini Sep 01 '14 at 09:24
  • @barryhoudini Yes, the numbers in column B are always 4 digits long. – leifericf Sep 01 '14 at 10:56
  • 2
    OK then if you want to stick with SUMIFS (which is more efficient than SUMPRODUCT) then using 2 criteria, ">=2000" and "<3000" is the same as saying "starting with 2" – barry houdini Sep 01 '14 at 11:07
  • Oh, my gosh… Can't believe I didn't think of that, @barryhoudini. I was so focused on making the Excel formula work in the way I had imagined, that I didn't see the obvious solution. Thanks for pointing it out to me. – leifericf Sep 01 '14 at 17:23

3 Answers3

3

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.

MP24
  • 3,110
  • 21
  • 23
  • Seems I accepted this answer too soon, due to an error on my part (thought I got it working, but upon closer inspection I hadn't). Seems like it should work, so I'm trying to figure out why this doesn't work for me now. Btw. I'm using Excel for Mac, don't know if it makes a difference. – leifericf Sep 01 '14 at 10:19
  • The cell with the formula in it just says `#VALUE!` with no specific error message, so I'm not exactly sure what's wrong. Still trying to figure it out. – leifericf Sep 01 '14 at 13:00
  • This will happen if any of your values in column `$C` is not a number. You may then want to use something like `$C2:$C10000` (same for columns A and B, respectively). – MP24 Sep 01 '14 at 13:15
  • Ah! There we go! Thanks, @MP24 — After changing all the whole column selectors to specific ranges it it started working (i.e. this works: `=SUMPRODUCT(($A2:$A13="Jack") * (LEFT($B2:$B13;1)="2") * ($C2:$C13)))`. – leifericf Sep 01 '14 at 13:19
  • 2
    Another way would be to allow `SUMPRODUCT` to do the multiplication by the sum range, rather than use *, for example this version will allow text in column C - `=SUMPRODUCT((A:A="Jack")*(LEFT(B:B)="2"),C:C)` – barry houdini Sep 01 '14 at 13:27
1

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

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • I can't seem to find the equivalent of Ctrl+Shift+Enter on Excel for Mac 2011. Do you happen to know how to insert an array formula in Excel for Mac? – leifericf Sep 01 '14 at 10:33
  • Thanks Jean-Francois - I was just repeating what I read - I've never used Excel on a Mac – barry houdini Sep 01 '14 at 11:22
  • Hmmm. I tried with Command+Return in the Mac version and nothing happened, but maybe it's due to my Mac's culture settings and keyboard layout being Norwegian or something like that (wouldn't be the first time these things cause weird issues in Excel). – leifericf Sep 01 '14 at 15:13
0
{=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

Sociopath
  • 13,068
  • 19
  • 47
  • 75
Rauf Waheed
  • 1