Excel: If Date Appears in Column, Count How Many Instances in Another 1

[ladyck3]

I'm creating a new report and having a bit of trouble.
I want the date in my new report, the SUMMARY worksheet, which is listed as 2/4/13, 2/5/23 and 2/6/13 across C2, D2 and E2, to be found on the RAW worksheet and then have the formula count how many times 1,2,3,4 and 5 appear for each date as data is added the RAW list will increase and of course the dates will run horizontally along.

The RAW data is on a worksheet called RAW

Col H Col N
2/4/2013 1
2/5/2013 5
2/6/2013 5
2/4/2013 4
2/5/2013 1
2/6/2013 2
2/4/2013 1
2/5/2013 2
2/6/2013 5

(SORRY, I have been trying for 20 minutes to get this to appear as 2 columns... don't know how but the N values are 1-5)

This is the formula I'm using:

=IF(Raw!$H:$H=Summary!C$2,COUNTIF(Raw!$N:$N,5))

Problem is.... of course.... this works GREAT but it counts the entire number of these scores for the entire listing of 98 items... it does not just pull the numbers for each specific date.

Where am I going wrong?

Please advise.

Thank you.... Laurie

ladyck3
aka: Laurie

 

[SkipVought]

Hi,

=if(isna(match(YourLookupValue,raw!$h:$h,0)),[no match result],[match result])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

SO this is how i laid it out.... and the results came back as all zeros...

=IF(ISNA(MATCH(C$2,Raw!$H:$H,0)),"",COUNTIF($N:$N,5))

No assumption... what did *I* do wrong?



ladyck3
aka: Laurie

 

[SkipVought]

1) what do you mean "all zeros"

2)what is the value in c2?

3) is that value in raw column h?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You can try SUMPRODUCT:
=SUMPRODUCT((Raw!$H1:H$100=Summary!C$2)*(Raw!$N1:N$100=5))

combo

 

[ladyck3]

Skip:

1) what do you mean "all zeros"
The results appear as 0, instead of counting the number of times 5 appears in N:N for the date in H:H C2 is the date on the summary page. I want whatever date on Summary Page (cellref on Line 2) to go to the RAW page and sound the number of 5's, 4's, 3's, 2's and 1's appear for that date.

H:H is a running list of dates, forever growing as data is added daily.

2)what is the value in c2? C2 is the location of the date being reported, so 02/04/2013, D2 is 02/05/2013, etc.

3) is that value in raw column h? I guess I answered that in 1) the RAW sheet will be added to daily so the Summary page is to automatically count the number of 5-1 per date as time goes on.

ladyck3
aka: Laurie