Excel, SUMIFS criteria range is also sum range

0
votes

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
2
excelexcel-formulaexcel-2010excel-2013sumifs
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
Just a pasting error, is present in production formula. Thanks for the comment though. – Garywoo

2 Answers

2
votes

@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)
1
votes

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))}