Can I use the indirect function in sumifs?

[amtrad]

Hi All

I am trying to use the sumifs formula to extract data from a table, one of the criteria that I'm attempting to use is a date and I want to sum up data that is equal to and greater than a date in a cell. This didn't work as the formula didn't like the cell reference, however when I included the date along with a greater than or equal sign directly within the forumla it worked. My issue is that I need to constantly change this date and I would like to be able to change one cell and the data updates rather than go and change the formula each time!

I thought that I could use the indirect function but no joy - am I on the wrong path? Can the sumifs accept the indirect formula?

Any help appreciated - BTW I don't do VBA. I have included the formula below for reference.

=SUMIFS(Data[AMOUNT],Data[DATE],">31/03/2010",Data[DATE],"<=31/12/2010",Data[NOMINAL_CODE],[@[ACCOUNT_REF]])

regards

Amtrad

 

[MrKowz]

Welcome to the forums!

Lets assume that A1 contains the date 31/03/2010 and A2 contains 31/12/2010. Then you can use:

=SUMIFS(Data[AMOUNT],Data[DATE],">"&A1,Data[DATE],"<="&A2,Data[NOMINAL_CODE],[@[ACCOUNT_REF]])

 

[amtrad]

It worked -thank you for your help!

regards

Amtrad