11

I'm trying to summarize data from several Google spreadsheet' sheets in a single one but I have some issues with SUMIF and FIND. To Sum data from another sheet I have to use this (at least that's the only solution I've found)

=SUM(INDIRECT(""&A6&""&"!E2:E"))

Where I have the name of my sheet on A6.This makes everything easy because I have a column (A) with all the names of the sheets. I haven't found something like Sheets[1] in Google Docs.

My next step was to Sum Times from a specific column but remove a specific values, which in this case is 1 that get transformed internally 24:00:00 since it's a time column:

=SUM(INDIRECT(""&A6&""&"!D2:D")) - SUMIF(INDIRECT(""&A6&""&"!D2:D");"24:00:00")

Here I tried to do everything with a single SUMIF but I have no idea how. NOT("24:00:00") didn't work so I settled to this. The last part is the most difficult and I'm not able to solve it. I have a column with month names (for example) and I want to sum some data only when the month name contains "er". Here is what I thought:

=SUMIF(A6:A16,ISNUMBER(Find("er")),G6:G16)

This gives me always 0 (Note that the last one contains A6:A16 instead of the whole INDIRECT part because I am still testing it in a single sheet.) Any Idea what I'm doing wrong?

player0
  • 124,011
  • 12
  • 67
  • 124
maugch
  • 1,276
  • 3
  • 22
  • 46
  • I believe that this question is relevant for you: http://stackoverflow.com/q/9128987/179529 – Guy Jul 04 '12 at 17:54

1 Answers1

16

I don't know why the above SUMIF doesn't work, what I've tested and works is:

=SUMIF(A6:A16,"*er*",G6:G16)

SUMIF is NOT SUM + IF as I thought. I hope it will help someone else.

maugch
  • 1,276
  • 3
  • 22
  • 46
  • 1
    It's been a while since this answer was accepted, but it should be noted that wildcards like * do not work on Google Sheets, as of January 2017 anyway. – Pablo D. Jan 01 '17 at 22:51
  • 2
    @Pablo not sure if they reverted or not but the * wildcard does still work in Google Sheets, as of March 2017 anyway. – mslhrt Mar 30 '17 at 13:15
  • 1
    Good to read that! Must have been a bad day for me or Google Sheets. – Pablo D. Apr 12 '17 at 19:06
  • Make sure that cells are formatted correctly for wildcards to work as criterion (plain text if wildcard'ing text). I thought wildcards are not supported anymore until I figured out the column was in Currency format. – gencay Nov 23 '21 at 19:10