Sumproduct - Multiple Criteria and Partial months

**dluhut** · 08-18-2014, 01:38 PM

Hello excel experts,

I have 3 columns that I need for my sumproduct calculation. However, the trick here is that, I want partial months to be calculated.

e.g. I have Date, Received Date and Activity.

Assuming that on Date - Aug 13, 2014, under activity "Update Service Completion Date", I have a total COUNT of 295.

Now I would like to dissect further to know, out of this 295, how many are > 90 days BASED (90 days is calculated based on Date - 90 + 1) on 'Received Date'. in each given month and year on 'Received Date'.

Hopefully my attached spreadsheet could help out more with what I'm trying to accomplish.

I tried to accomplish this with Pivot Table, but it's giving me ALL of the months.

**oeldere** · 08-18-2014, 01:52 PM

A small example will do (instead of > 7.000 rows).

Make a formula in the raw data sheet, that determine the range.

After that you will be able to use that (new) column in your pivot table.

**dluhut** · 08-18-2014, 01:53 PM

Originally Posted by oeldere

A small example will do (instead of > 7.000 rows).

Make a formula in the raw data sheet, that determine the range.

After that you will be able to use that (new) column in your pivot table.

I was thinking of using a helper column. But still that doesn't fix the PER 'Date' and PER 'Received Date'.

Would appreciate if you could show me how you'd do it through a helper column.

For example, for DATE Aug 13, I would be interested in the RECEIVED DATE May 16 date and older.
but for DATE Aug 4, I would be interested in the RECEIVED DATE May 9 date and older.

In the helper column, how should it knows but to take, especially, in my pivot, I have the DATE go across the row, and RECEIVED DATE go down the column.

**oeldere** · 08-18-2014, 02:02 PM

How about, putting it manualy in your (small) sheet, so I could see what you mean?

Sumproduct - Multiple Criteria and Partial months

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct - Multiple Criteria and Partial months

Re: Sumproduct - Multiple Criteria and Partial months

Re: Sumproduct - Multiple Criteria and Partial months

Re: Sumproduct - Multiple Criteria and Partial months

Thread Information

Users Browsing this Thread

Similar Threads

Calculating Months/Yr including partial months

Trying Sumproduct with multiple criteria 1) greater than, or 2) partial text

[SOLVED] Sumproduct with multiple criteria including partial search

Excel 2007 : SUMPRODUCT() partial criteria lookup using LEFT() and INDIRECT() - Fails to Load Range

Sumproduct for multiple months

Bookmarks

Bookmarks

Posting Permissions