0

I'm trying to find the total sum of values in column I in another workbook greater than the value of £179 [referenced in cell B13] between a date range in column H 01/06/2014 - 30/06/2014 [referenced in cells B1 and C1 respectively]. I don't want to edit the workbook containing columns H and I.

This is the formula i have but it errors because i assume you can't have the sum range also a criteria range: =SUMIFS([anotherFilel.xlsx]sheet!$I$16:$I$99999, [anotherFilel.xlsx]sheet!$I$16:$I:$99999,>$B$13,[anotherFilel.xlsx]sheet!$H$16:$H$99999,<=$C2,[anotherFilel.xlsx]sheet!$H$16:$H$99999,>=$B2)

And the anotherFile.xlsx sheet: (contains blank rows)

H           I
30/05/14    £1,072.00
















16/04/14    £179.00












25/04/14    £249.00

28/04/14    £169.50

            £358.00
30/04/14    £179.00
02/05/14    £1,146.00

01/05/14    £179.00
30/04/14    £796.00

01/05/14    £150.00



06/05/14    £179.00



08/05/14    £278.00
08/05/14    £1,440.00



19/05/14    £249.00


20/05/14    £179.00











02/06/14    £127.00
02/06/14    £269.00

05/06/14    £297.83
04/06/14    £23.50

12/06/14    £214.80
04/06/14    £318.00

25/06/14    £144.00


18/06/14    £538.00
12/06/14    £155.75



09/06/14    £298.00




17/06/14    £597.60


13/06/14    £302.00





17/06/14    £264.00
17/06/14    £49.50
17/06/14    £23.50











23/06/14    £134.00
Garywoo
  • 607
  • 1
  • 6
  • 9
  • looks like you might be missing a '[' bracket before second anotherFile1.xlsx reference. Not sure if thats the problem or just from pasting it over – tgeery Jun 25 '14 at 00:42
  • Just a pasting error, is present in production formula. Thanks for the comment though. – Garywoo Jun 25 '14 at 01:06

2 Answers2

2

@Fabricator's answer should work, but if you want to write it with just SUMIFS, you need to quote your conditions and concatenate your values with &. Excel doesn't have any problems with a column being both a condition / the summing column.

=SUMIFS([anotherFilel.xlsx]sheet!$I$16:$I$99999, 
        [anotherFilel.xlsx]sheet!$I$16:$I$99999, ">" & $B$13,
        [anotherFilel.xlsx]sheet!$H$16:$H$99999, "<=" & $C2,
        [anotherFilel.xlsx]sheet!$H$16:$H$99999, ">=" & $B2)
chrisb
  • 49,833
  • 8
  • 70
  • 70
1

You can use array formula

{=SUM(IF((B1<=[anotherFilel.xlsx]sheet!H:H)
            *([anotherFilel.xlsx]sheet!H:H<=C1)
            *([anotherFilel.xlsx]sheet!I:I>B13),
         [anotherFilel.xlsx]sheet!I:I,0))}
Fabricator
  • 12,722
  • 2
  • 27
  • 40
  • I don't want to edit the file with rows H and I (A:A and B:B in your example formula), just the sheet that's referencing them. – Garywoo Jun 25 '14 at 01:19
  • Thanks but wont that still have to be edited into the workbook containing columns `H` and `I`? I would like to leave that file as is and only edit the formula that references them in another workbook. I updated the OP to explain a little further. – Garywoo Jun 25 '14 at 01:29
  • @Garywoo, can't you just add the file reference like you did originally? – Fabricator Jun 25 '14 at 01:35
  • I'm sure this works but i just wasn't familiar with array formulas and couldn't get it to work. @chrisb 's answer worked great. – Garywoo Jun 25 '14 at 02:13
  • @Garywoo, that's okay. Just copy the formula without `{}`, and hit `Ctrl+Shift+Enter`. – Fabricator Jun 25 '14 at 02:21