Sum IF and Countif Combined based on Separate Sheets

I don't understand your results. If I filter the Data for Dave I see he has 1 Re-order, 4 First Sales and 3 Returned, vs your 0,2,3.

Please clarify and add some more results.

Have you considered a Pivot Table?

Hi Richard you're correct

13/08/2016 Dave Returned
14/08/2016 Dave FirstSale
16/08/2016 Dave FirstSale
20/08/2016 Dave FirstSale
27/08/2016 Dave Reorder


I had the filter set up wrong. Only looking for the last 5 sales

So should be 1 Returned, 3 Firstsale and 1 Reorder.

This proposed solution employs a helper column (E) which could be moved for convenience and/or hidden for aesthetic purposes.
The array entered formula* that populates the helper column is:

Formula:

Please Login or Register  to view this content.

The formula that populates the table is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
Let us know if you have any questions.

Hi,

I've added a Pivot Table solution which you may find gives you a lot more flexibility.
I added a helper column to your data to identify the last 5 - it assumes the data is in ascending order of dates.
I've also created a dynamic range name 'Data' which will automatically adjust to cater for new data.
There's a one line macro which will refresh the Pivot Table every time you go to look at it. Hence this is a .xlsm file type

Use the Slicers to filter the PT by Name or Sale Type and if you want to see all Sales not just the last 5 remove the Filter on the 'Last 5' slicer.

...and forgot to add that if you want the Sales Types in columns in the PT just drag the Sales Type field from the Row Labels area of the PT Field List to the Column Labels area.