Help with COUNTIF

[systemx]

Hi all,

I am desperate for some help with a formula - hopefully simple but I
cannot get my head around it at the moment. I will try to make my words
make sense....

I have two worksheets - worksheet A and worksheet B.

Worksheet 1 contains a date as text (ddmmyy) eg 080306 in column A
Worksheet 2 contains a text value in column B (either "C" or "G")

Worksheet 2 contains a date as text in column A
Worksheet 2 contains a formula in column B

The formula on worksheet B, should count the number of C's appearing on
worksheet 1, if the dates match.

This is what I came up with for the formula on worksheet 2 -

=IF(A:A='Worksheet 1'A:A, COUNTIF('Worksheet 1'J:J,"C"),0)

When I leave the cell range open (eg A:A) - the formula will count ALL
of the cells containing "C" - not just the ones where the date
matches.

Have I missed something obvious here? Or do I need to use a more
complex formula?

Thank you

Rob

 

[Dav]

=IF(A:A='Worksheet 1'A:A, COUNTIF('Worksheet 1'J:J,"C"),0)

Unfortunately sumproduct does not like full coulms as ranges

Try sumproduct(('worksheet 1'!$a$2:$a$65536=A2)*('worksheet
1'!$b$2:$b$65536="c")) and then copy it down

Regards

Dav

 

[Bob Phillips]

=SUMPRODUCT(--('Worksheet A'!A2:A1000=A2),--('Worksheet A'!B2:B1000="C"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[systemx]

Thank you both for your help!

The formula both worked perfectly once applied - I really appreciate
this.

If I could be so cheeky to ask one more question - being new to the
world of arrays - I have populated the formula down around 250 cells
across a range of 10 columns (the example I gave was just a cut back
version of what was required).

Performance with excel drops significantly when using arrays and the
file size also seems so increase dramatically.

Does anyone know of any information/tutorials relating to arrays and
how to use them and maintain performance levels? Or can anyone provide
any tips based on experience?

Thanks again!

Rob

 

[Bob Phillips]

Best place to look is at http://www.decisionmodels.com/calcsecrets.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)