sumproduct and countif question

reducedjuice

Board Regular
Joined
Oct 29, 2008
Messages
99
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 :cool:
 

reducedjuice

Board Regular
Joined
Oct 29, 2008
Messages
99
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,036
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!
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,036
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

Board Regular
Joined
Oct 29, 2008
Messages
99
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,036
Can you provide a small sample of the relevant data, and post the formulas you're using?
 

reducedjuice

Board Regular
Joined
Oct 29, 2008
Messages
99
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
ABCDEF
1TBS IDCreated OnDate XDate YNumber of TransactionsTotal Profit
2WEB011926319-Mar-0719-Feb-0716-Apr-07??
3WEB009479220-Aug-0823-Jul-0817-Sep-08
4WEB011974326-Aug-0829-Jul-0823-Sep-08
5WEB011372205-Sep-0808-Aug-0803-Oct-08
Sheet1
Copy of Book1.xls
ABCD
1transdateordernumTBS IDprofit
231-Oct-07W00340400AWEB000693511.29
331-Oct-07W00340406AWEB000693555.55
431-Oct-07W00340373AWEB000694213.3
531-Oct-07W00340344AWEB000814560
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,036
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

Board Regular
Joined
Oct 29, 2008
Messages
99
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,036
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)
 

Forum statistics

Threads
1,085,056
Messages
5,381,471
Members
401,741
Latest member
MattA8

Some videos you may like

This Week's Hot Topics

Top