# SUMIFS w/ INDIRECTS?

1. ## SUMIFS w/ INDIRECTS?

I'm trying to create a SUMIFS formula by getting data based on unique cell values.

The following formula grabs what I need but instead of "=BNG0726" I would like to use a value in a cell so that the formula changes based on an entered value.

Also I would like to add a date criteria the same way. For example if date is greater or equal to a value in a particular cell and less than or equal to a value in another cell it will grab the data in cell DATA K1:K100000.

Here's my formula below. It grabs the data I need but would have to be manipulated to reference the correct account code. Also I couldn't get the date criteria to work.

=SUMIFS(DATA!\$K\$1:\$K\$100000,DATA!\$B\$1:\$B\$100000,">629999",DATA!\$B\$1:\$B\$100000,"<640000",DATA!\$A\$1:\$A\$100000,"=BNG0726")

Here's my attempt to add date criteria but it didn't produce the correct result.

=SUMIFS(DATA!\$K\$1:\$K\$100000,DATA!\$B\$1:\$B\$100000,">629999",DATA!\$B\$1:\$B\$100000,"<640000",DATA!\$A\$1:\$A\$100000,"=BNG0726",DATA!\$D\$1:\$D\$100000,"">="&DATE(4/1/2012)",DATA!\$D\$1:\$D\$100000,""<="&DATE(3/31/2013)")  Register To Reply

2. ## Re: SUMIFS w/ INDIRECTS?

Assuming 629999 is in A1, 640000 is in B1 and BNG0726 in in C1,

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: SUMIFS w/ INDIRECTS?

A variation, think you could also try using sumproduct [but use the smallest explicit range]:

=SUMPRODUCT((Data!\$A\$1:\$A\$1000=\$A2)*(Data!\$B\$1:\$B\$1000>\$A3)*(Data!\$B\$1:\$B\$1000<\$A4)*(Data!\$D\$1:\$D\$1000>=\$A5)*(Data!\$D\$1:\$D\$1000<=\$A6),Data!\$K\$1:\$K\$1000)

where the relevant specs are input into A2:A6
(real dates are presumed specified in A5 and A6)
and the desired SumRange is col K  Register To Reply

4. ## Re: SUMIFS w/ INDIRECTS?

Thanks Max! That worked well. Is there a max range for SUMPRODUCT?

How do I change to resolved?  Register To Reply

5. ## Re: SUMIFS w/ INDIRECTS?

welcome, glad to hear. you could express your satisfaction with the answer by clicking the little star at the bottom left of my response > .. Is there a max range for SUMPRODUCT?
well, the practical reason why its always best to use the smallest explicit range is simply to optimize calculation/recalc speed, as the sumproduct function is v.calc-intensive in nature. The greater the range covered, the heavier the calc load.  Register To Reply

6. ## Re: SUMIFS w/ INDIRECTS?

Max, why suggest sumproduct?

Sumifs can be used if it is written correctly and is far more efficient.

=SUMIFS(Data!\$K:\$K,Data!\$A:\$A,\$A2,Data!\$B:\$B,">"&\$A3,Data!\$B:\$B,"<"&\$A4,Data!\$D:\$D,">="&\$A5,Data!\$D:\$D,"<="&\$A6)  Register To Reply

7. ## Re: SUMIFS w/ INDIRECTS?

Actually why the Q? If you read my response carefully, it didn't hint on the SP option being superior. Anyway, this is one good reason - Sumproduct works across all Excel versions, unlike SUMIFs. If one routinely exchanges Excel files with others in the course of work - where various lower Excel versions (especially Excel 2003) are still in active use, its always safer to use SP. Um, another reason, my Excel 2003 doesn't have the new function SUMIFs   Register To Reply

8. ## Re: SUMIFS w/ INDIRECTS?

Actually it was more a case of wondering why you deviated from sumifs when the OP was already using it, which would indicate that backwards compatibility would not be necessary.  Register To Reply

9. ## Re: SUMIFS w/ INDIRECTS?

Granted, but I don't think my suggesting the SP option in this thread caused any harm to the OP. Its always good to know of alternatives.  Register To Reply

10. ## Re: SUMIFS w/ INDIRECTS? Originally Posted by HCBalelo =SUMIFS(DATA!\$K\$1:\$K\$100000,DATA!\$B\$1:\$B\$100000,">629999",DATA!\$B\$1:\$B\$100000,"<640000",DATA!\$A\$1:\$A\$100000,"=BNG0726",DATA!\$D\$1:\$D\$100000,"">="&DATE(4/1/2012)",DATA!\$D\$1:\$D\$100000,""<="&DATE(3/31/2013)")
Probably better to use cell references for the dates as you already suggested but the problem, specifically, with this formula is the date criteria, you need to use DATE function like this:

">="&DATE(2012,4,1)  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 