Look Up and Count

[jtinne]

I'm using Exel 2007 and need some help creating a formula. Here's what I'm
trying to do.

The spreadsheet has two sheets, the first sheet in column C has specific
dates and column D has drop down list with a selection of descriptions.
Sheet 2 has a week of date in column A (exp 06-02-2008) and the following
columns (B, C, D etc) have each of the selections of the drop down list from
sheet 1.

What I want to do is on sheet 2 in each column next to the week of date find
all the dates within that week and count how many times that specific
description comes up during that week and display the total under that
decription in line with that week of.

Any help is appreciated, please feel free to ask if you have any questions,
thanks!

 

[Ted M H]

Can you provide additional information to help me visualize the problem?
Doing a little spreadsheet in your post would help me understand what you
want to do:

Sheet 1
A B C D
1
2
3
4

Something like this for each of your sheets, with data provided to
illustrate. Takes more time but makes it easier (possible) to understand the
question.

If sounds like or COUNTIFS could probably help you out here, using Col C on
first sheet as the criteria range. You might be able to set the first
criteria as >= week-of date and <= week-of date+7 and the second criteria as
= the cell with the description you want to count.

 

[jtinne]

Here's the layout of the sheets. For instance, on sheet 2 cell C2 needs to
return a value of 2 because during that week "Descrip 2" occured twice. Cell
D3 needs to return a 1 for the one occurance that week, cell B1 would need to
return a 1 as well. On sheet 1, the A and B column are irrelavent to the
values need on sheet 2, also the dates on sheet 1 will not be in any kind of
order. I hope this helps.

Sheet 1
A B C D
1 Brand Num Date Description
2 XYZ 123 6/10/08 Descrip 3
3 YYZ 234 6/3/08 Descrip 2
4 XYZ 123 6/5/08 Descrip 2
5 XZZ 456 6/2/08 Descrip 1

Sheet 2
A B C D
1 Week Of Descrip 1 Descrip 2 Descrip 3
2 6/2/08
3 6/9/08
4 6/16/08

 

[Ted M H]

Great problem description!

Here's a formula for you:

=COUNTIFS(Sheet1!$D:$D,"="&B$1,Sheet1!$C:$C,">="&$A2,Sheet1!$C:$C,"<"&$A2+7)

You'd plug this into B2 on Sheet2, assuming the week-of dates populate
column A on sheet2 and the Descriptions populate row 1 on sheet2. The mixed
cell references should make it possible to copy the formula across and down
as many columns and rows as you need without having to change the formula

What this does the following:
For the first criteria, the formula requires a match in sheet1 column D to
the column heading in sheet 2 (Desc 1, for example).
The next criteria requires the Date in Col C Sheet1 to be >= the date in
Sheet2 Col A(week of 6/2/08, for example)
The last criteria requires the Data in Col C sheet1 to be < 7 days after the
date in Sheet2 Col A. These last two criteria just check that the date in
Sheet1 Col C falls somewhere in the Week-off on Sheet2.

Just make sure that the Desc column headings in sheet2 Row 1 are coordinated
with the Desc values in Sheet1 Col D (i.e. they need to be spelled the same,
etc.), and that you have valid dates everywhere.

 

[jtinne]

Awesome, works perfectly, thank you!

Great problem description!

Here's a formula for you:

=COUNTIFS(Sheet1!$D:$D,"="&B$1,Sheet1!$C:$C,">="&$A2,Sheet1!$C:$C,"<"&$A2+7)

You'd plug this into B2 on Sheet2, assuming the week-of dates populate
column A on sheet2 and the Descriptions populate row 1 on sheet2. The mixed
cell references should make it possible to copy the formula across and down
as many columns and rows as you need without having to change the formula

What this does the following:
For the first criteria, the formula requires a match in sheet1 column D to
the column heading in sheet 2 (Desc 1, for example).
The next criteria requires the Date in Col C Sheet1 to be >= the date in
Sheet2 Col A(week of 6/2/08, for example)
The last criteria requires the Data in Col C sheet1 to be < 7 days after the
date in Sheet2 Col A. These last two criteria just check that the date in
Sheet1 Col C falls somewhere in the Week-off on Sheet2.

Just make sure that the Desc column headings in sheet2 Row 1 are coordinated
with the Desc values in Sheet1 Col D (i.e. they need to be spelled the same,
etc.), and that you have valid dates everywhere.