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]

I actually got very close here with the COUNTIFS (and SUMIFS) function, except hit a roadblock.

Syntax for SUMIFS: SUMIFS(sum_range,criteria_range1,criteria1...)

I need my criteria1 to be a cell reference so I can fill the formula down about 200k rows of data, but it doesn't seem to want to accept that. All the Help files I've read seem to suggest that criteria1 can't be a cell reference - any ideas?

 

[Domenic]

Try...

=COUNTIFS(CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3=Criteria3)

=SUMIFS(SumRange,CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3=Criteria3)

However, if COUNTIFS and SUMIFS behaves as COUNTIF and SUMIF for prior versions of Excel (I don't know since I don't have Excel 2007), then you'll get an #VALUE! error when referencing a closed workbook. If this is the case, try SUMPRODUCT instead...

=SUMPRODUCT(--(DateRange>=CellReference),--(DateRange<=CellReference),--(CustomerRange=CellReference))

=SUMPRODUCT(--(DateRange>=CellReference),--(DateRange<=CellReference),--(CustomerRange=CellReference),RangeToSum)

Hope this helps!

 

[Domenic]

Try...

=COUNTIFS(CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3=Criteria3)

=SUMIFS(SumRange,CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3=Criteria3)

Make that...

=COUNTIFS(CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3,Criteria3)

=SUMIFS(SumRange,CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3,Criteria3)

 

[reducedjuice]

Make that...

=COUNTIFS(CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3,Criteria3)

=SUMIFS(SumRange,CriteriaRange1,">="&CellReference,CriteriaRange2,"<="&CellReference,CriteriaRange3,Criteria3)

Domenic,

Thanks for the reply! Your corrected formula worked to reference the cell values, so that's good. But, it has returned values of all zeroes when there should be other values.

For the SumRange, does it only sum up the values within the range in rows where the other criteria is met? Not sure if that makes sense...

Appreciate the help

 

[Domenic]

Can you provide a small sample of the relevant data, and post the formulas you're using?

 

[reducedjuice]

I'm just figuring out how to post the example, so pls bear with me if it shows up screwy. I have posted 2 examples to show the worksheets I am trying to link the formula between.

Copy of Book1.xls
	A	B	C	D	E	F
1	TBS ID	Created On	Date X	Date Y	Number of Transactions	Total Profit
2	WEB0119263	19-Mar-07	19-Feb-07	16-Apr-07	?	?
3	WEB0094792	20-Aug-08	23-Jul-08	17-Sep-08
4	WEB0119743	26-Aug-08	29-Jul-08	23-Sep-08
5	WEB0113722	05-Sep-08	08-Aug-08	03-Oct-08
Sheet1

Copy of Book1.xls
	A	B	C	D
1	transdate	ordernum	TBS ID	profit
2	31-Oct-07	W00340400A	WEB0006935	11.29
3	31-Oct-07	W00340406A	WEB0006935	55.55
4	31-Oct-07	W00340373A	WEB0006942	13.3
5	31-Oct-07	W00340344A	WEB0008145	60
Sheet2

Here is what I'm trying to do:

In WS1, column A contains unique values of customer numbers. WS2 contains transactional data where each row is a seperate transaction, and column C also has the customer number, though here a customer number may appear on multiple rows depending on how many transactions they have completed.

Back to WS1, in cell E2, I am trying to draw up a formula that looks at multiple criteria and retruns a result. If WS1 A2 exists in WS2 column C, and the date in WS2 A2 is greater than WS1 C2, and the date in WS2 A2 is less than WS1 B2, then calculate the number of instances of that (ie. how many transactions did the specified customer complete within the specified date range). Here is the formula I used in cell E2 Excel 2007:

=COUNTIFS(Sheet2!C2:C5,"="&A2,Sheet2!A2:A5,">="&C2,Sheet2!A2:A5,"<"&B2)

It is simply returning values of "0" for all 100K rows of data in that column, so something isnt right.

Similarly, I'm trying to sum the profit for transactions that fit the same criteria using the following formula:

=SUMIFS(Sheet2!D2:D5,Sheet2!C2:C5,"="&A2,Sheet2!A2:A5,">="&C2,Sheet2!A2:A5,"<"&B2)

Seriously appreciate any thoughts you have on this.

Cheers.

 

[Domenic]

In Sheet1, find a cell housing an ID. As an example, let's say A2. In Sheet2, find a cell housing the same ID. Let's assume that C10 contains the same ID, try...

=Sheet1!A2=Sheet2!C10

What does the formula return, TRUE or FALSE?

 

[reducedjuice]

In Sheet1, find a cell housing an ID. As an example, let's say A2. In Sheet2, find a cell housing the same ID. Let's assume that C10 contains the same ID, try...

=Sheet1!A2=Sheet2!C10

What does the formula return, TRUE or FALSE?

In this scenario the formula returns TRUE

 

[Domenic]

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)