SumIF or SumProduct
 

I have a spreadsheet ( Excel 2003 ) that has Column A filled with company
names and column B with dates. Company A appears 20 times followed by
company B 10 times and so on in column A. Column B contains dates that
orders were placed by these companies. I have created a PivotTable that that
runs a count and displays Company Name in the row fields area and count for
that company in the data field area. I would like to run a second count so
that I can identify the number of times company A appears before a specific
date from column B. It should look like this:

Desired PivotTable Layout - I have the first two columns already
Name Count of Company Name Occurances/Comp A Before
"Date"
Company A 20 3
Company B 10 7
Company C 17 4

Thanks in advance for the help.

SumIF or SumProduct
 

Hi,

=SUMPRODUCT((C2:C3="a")*(D2:D3<DATE(2009,12,16)))

change the range and the dates to fit your needs

if this helps please click yes thanks

"DS" wrote:

I have a spreadsheet ( Excel 2003 ) that has Column A filled with company
names and column B with dates. Company A appears 20 times followed by
company B 10 times and so on in column A. Column B contains dates that
orders were placed by these companies. I have created a PivotTable that that
runs a count and displays Company Name in the row fields area and count for
that company in the data field area. I would like to run a second count so
that I can identify the number of times company A appears before a specific
date from column B. It should look like this:

Desired PivotTable Layout - I have the first two columns already
Name Count of Company Name Occurances/Comp A Before
"Date"
Company A 20 3
Company B 10 7
Company C 17 4

Thanks in advance for the help.

SumIF or SumProduct
 

Best way is to add a new field into your source data. Call it 'Date Included'
or whatever...

The new field will have a formula similar to

=B2 = Sheet1!A1 this returns True or False

Sheet 1 A1 will have the date in question. Now you can just add this new
field to your pivot and refresh the pivot.
--
HTH...

Jim Thomlinson


"DS" wrote:

I have a spreadsheet ( Excel 2003 ) that has Column A filled with company
names and column B with dates. Company A appears 20 times followed by
company B 10 times and so on in column A. Column B contains dates that
orders were placed by these companies. I have created a PivotTable that that
runs a count and displays Company Name in the row fields area and count for
that company in the data field area. I would like to run a second count so
that I can identify the number of times company A appears before a specific
date from column B. It should look like this:

Desired PivotTable Layout - I have the first two columns already
Name Count of Company Name Occurances/Comp A Before
"Date"
Company A 20 3
Company B 10 7
Company C 17 4

Thanks in advance for the help.