Multiple values in one cell for sumif function

**bernard.x** · 11-08-2010, 06:13 AM

Hi, I need to use the sumif function to return some no., however some of the criteria cell has more than one value (range of values), so how can I key the criteria cell so that the sumif function returns the value? Please help. Many thanks !

E.g.
GL Amount
1000 =sumif(xxx
1800-2000
2100-2110

**arthurbr** · 11-08-2010, 06:16 AM

And what is your criteria to sum?

**bernard.x** · 11-08-2010, 07:10 AM

The criteria is the GL no. e.g. 1000, 1800, 2000,etc. There is a correspondence amount for each GL, in another tab, however I'm unable to use the sumif function in this tab because the GL no. is a range, e.g. from 1800 to 2000. Please see the attachment.

**daddylonglegs** · 11-08-2010, 07:34 AM

It might be easier to split your ranges out into 2 columns then you could use a regular SUMIFS formula, otherwise.....

Assuming the ranges are in column A, always separated by a hyphen and the amount to sum in column B then with a specific GL in D2 use this formula in E2

=SUM(IF((D2>=IFERROR(LEFT(A$1:A$100,FIND("-",A$1:A$100)-1)+0,0))*(D2<=IFERROR(REPLACE(A$1:A$100,1,FIND("-",A$1:A$100),"")+0,0)),B$1:B$100))

confirmed with CTRL+SHIFT+ENTER

**zbor** · 11-08-2010, 08:01 AM

Or, as daddylongs already said, if you can write only first numbers in your cells.. Your formula might become much simplier:

(note that if you write 1000-1800, 1810-2200, 2210 - ...) is same as up to 1000, 1800, 2200...

**bernard.x** · 11-08-2010, 08:11 AM

Hi, thanks a lot, but I need to sum up the GL range in a different tab, the original trial balance consist of the amount for each GL, but in the output tab, I need to sum up the GLs. Please see the enclosed attachment.

**daddylonglegs** · 11-08-2010, 08:35 AM

OK, I misunderstood, assumed you had ranges in the data not as criteria. If you use your original approach you can use this formula in C14 copied down

=SUMIF('Trial Balance'!A:A,">="&A14,'Trial Balance'!B:B)-SUMIF('Trial Balance'!A:A,">"&B14,'Trial Balance'!B:B)

or in Excel 2007 you can use SUMIFS like this for the same result

=SUMIFS('Trial Balance'!B:B,'Trial Balance'!A:A,">="&A14,'Trial Balance'!A:A,"<="&B14)

Note: those formulas both make the range "inclusive", i.e. with a range 1600-2000 both 1600 and 2000 will be included

**bernard.x** · 11-08-2010, 08:41 AM

Originally Posted by daddylonglegs

OK, I misunderstood, assumed you had ranges in the data not as criteria. If you use your original approach you can use this formula in C14 copied down

=SUMIF('Trial Balance'!A:A,">="&A14,'Trial Balance'!B:B)-SUMIF('Trial Balance'!A:A,">"&B14,'Trial Balance'!B:B)

or in Excel 2007 you can use SUMIFS like this for the same result

=SUMIFS('Trial Balance'!B:B,'Trial Balance'!A:A,">="&A14,'Trial Balance'!A:A,"<="&B14)

Note: those formulas both make the range "inclusive", i.e. with a range 1600-2000 both 1600 and 2000 will be included

Hi, oh yes, the above formula works. Thanks a lot guys (zbor & daddylong). Cheers

On a related note, if the GL no. is not a range, e.g. 1800 & 2000, any suggestion on how the formula should be? I know I can sum up the two GLs by changing the formula, but I will like to avoid doing so, as it would be tedious and hard to keep track. I am thinking of adding more columns by the side or use a lookup against another small table, any suggestions please? Thanks.