SUMPRODUCT with date restrictions

**jayres14** · 07-12-2012, 05:15 AM

I am trying to keep track of trips for my records. I need some help doing so by using the SUMPRODUCT formula. I have it able to count the total number of times a state appears in column B, even if I went to two states in one trip, it counts as 1 trip. However, I am trying to further track that by years of the trips. Any help would be appreciated. I have attached an example to show what I want and what I have so far.

**Fotis1991** · 07-12-2012, 07:05 AM

Hi

One way, could be this.

In J3 and in K3, type the States that you are looking for.

In D3 and copy down, use this.

Please Login or Register  to view this content.

ThiS is a helper column and you can hide this.

Then in K4, you get your result, using this.

Please Login or Register  to view this content.

Is this works for you?

**Marcol** · 07-12-2012, 07:33 AM

Try this in K4

Formula:

Please Login or Register  to view this content.

Or for 2007 and above

Formula:

Please Login or Register  to view this content.

However I'm not sure it is wise to use SUMPRODUCT() with whole columns.

**jayres14** · 07-13-2012, 03:07 AM

okay so I may have miss lead you a little. This did help and bring me closer to my goal. However, I am alo trying to track the trips by region, year and quarter. Can anyone help with this matter? I have attached a better workbook that shows my goals.

**Marcol** · 07-13-2012, 07:27 AM

You're making life hard with your sheet layout and data types, concatenating initial data is seldom a good idea, nor is merging cells.

This is a bit of a guddled compromise, see if it returns the results you want.
If it does we can try to simplify the process.