sumproduct and countif question

[reducedjuice]

Hi All,

Can anyone pls assist with the following? I'll try and lay this out as clearly as possible:

• I have 2 different worksheets, WS1 and WS2 (in different files but that doesn't matter)

• In WS1 Column A, I have a list of customer numbers. All values are unique, and there are no duplicates.
• In WS1 Column B, I have a date of a certain event for each customer
• In WS1 Column C, I have another date 28 days prior to the date in Column B (so, simply =B2-28)
• In WS1 Column D, I have another date 28 days ahead of the date in Column B (so, =B2+28)

• In WS2, I have transactional data for customers. Each row represents a unique transaction. A customer may have zero or multiple transactions.
• In WS2 Column A, the Customer Number for that transaction appears in the same format as WS1 Column A
• In WS2 Column B, the transaction date appears
• In WS2 Column C, the profit for the transaction appears

With all that, I'm trying to accomplish 2 things and feel like I could probably use a SUMPRODUCT or SUM/IF/AND combo, but haven't had much luck.

1. If the Customer Number in WS1 Column A appears in WS2 Column A AND the transaction date in WS2 Column B is between the date range in WS1 Columns B and C, then I want it to return the number of instances (ie. transactions) there were for that specified customer in that date range.
2. Using the same parametres as above, I want it to return the sum of all profit on the transactions that fell within that date range.

Thanks for taking a look

 

[reducedjuice]

Okay, now let's make sure that the dates are true date values. So, for example, the following should return TRUE...

=ISNUMBER(Sheet1!B2)

=ISNUMBER(Sheet1!C2)

=ISNUMBER(Sheet2!A2)

=ISNUMBER(Sheet1!B2) - TRUE

=ISNUMBER(Sheet1!C2) - TRUE

=ISNUMBER(Sheet2!A2) - FALSE

I suppose that's part of the problem then... I need to match the formatting of that column to that of the other two, correct? Or vice versa I guess.

 

[Domenic]

It looks like the date in Sheet2!A2 is being recognized as text. The following should convert the date to a true date value...

1) Select an empty cell

2) Edit > Copy

3) Select Sheet2!A2 (or the entire column to convert them to true date values)

4) Edit > Paste Special > Add > Ok

Does this help?

 

[reducedjuice]

It looks like the date in Sheet2!A2 is being recognized as text. The following should convert the date to a true date value...

1) Select an empty cell

2) Edit > Copy

3) Select Sheet2!A2 (or the entire column to convert them to true date values)

4) Edit > Paste Special > Add > Ok

Does this help?

That does help! It converted it and I ran the test you gave me which returned TRUE this time. I ran my SUMIFS formula and it returned results... I took a small sample and manually calculated to ensure it was done properly and it is!

Thanks for your help and patience!

 

[Domenic]

You're very welcome! Thanks for the feedback!