Using SUMIFS with only the last few data of a column ?

**iparout** · 12-19-2011, 03:38 PM

Hi.

I am posting a sample worksheet and a screenshot from it to make my point more clear.

Sample.jpg

Sales.xlsx

I have successfully used SUMIFS in order to calculate the income of a specific salesperson up to the date I am interested (in our example, E17 represents the total income of Tom up to the 29th of August).

What I want to do now is :

1) Be able to use SUMIFS in order to sum up only the last 4 of Tom's entries. In our example, for the 29th of August, this should add up D17+D14+D13+D11)

2) Be able to use SUMIFS in order to sum up all entries of Tom for the last month (30 calendar days). In our example, for the 4th of September, this should add up D19+D17+D14+D13+D11+D10+D7).

Is there any way to do this or am I asking too much ?

Thanks a lot.

**rscsmith** · 12-19-2011, 06:55 PM

Hi

Item 2 is fairly easy. Enter the following in to your sample spreadsheet:-

Please Login or Register  to view this content.

See attached example. (Note - I've changed the dates on your test data)

I couldn't think of an easy way to do part 1. One option is to use UDFs (User Defined Function - i.e. VBA). The only other alternative I could think of is hidden columns and some pretty hairy formulae. Which would you prefer? We can write you the UDF if you want, but I'm out of time at the moment.

Cheers, Rob.

**MarvinP** · 12-19-2011, 07:19 PM

Hi iparout,

See if the attached helps. It will calculate the last 4. I've used a helper column, but I believe you can do the helper within a single formula.

**iparout** · 12-19-2011, 07:40 PM

Thanks, Rob... Works like a charm ! What I didn't know is that counting back X days was as easy as DATE-X.

**iparout** · 12-19-2011, 07:45 PM

Originally Posted by MarvinP

Hi iparout,

See if the attached helps. It will calculate the last 4. I've used a helper column, but I believe you can do the helper within a single formula.

It does work but it seems kinda complex for my naive mind !

Thank you very much for helping me out !

**MarvinP** · 12-19-2011, 07:48 PM

Hi iparout,

Look at the helper column and see how that CountIf formula works. It always starts from the top and goes down to the current row to do its count. The Helper Column is very useful in many of these kinds of problems.