software

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 quot;Cquot; or quot;Gquot;)

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,quot;Cquot;),0)

When I leave the cell range open (eg A:A) - the formula will count ALL
of the cells containing quot;Cquot; - 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--
systemx
------------------------------------------------------------------------
systemx's Profile: www.excelforum.com/member.php...oamp;userid=29254
View this thread: www.excelforum.com/showthread...hreadid=519644
=IF(A:A='Worksheet 1'A:A, COUNTIF('Worksheet 1'J:J,quot;Cquot;),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=quot;cquot;)) and then copy it down

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=519644=SUMPRODUCT(--('Worksheet A'!A2:A1000=A2),--('Worksheet A'!B2:B1000=quot;Cquot;))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;systemxquot; gt; wrote in
message ...
gt;
gt; Hi all,
gt;
gt; I am desperate for some help with a formula - hopefully simple but I
gt; cannot get my head around it at the moment. I will try to make my words
gt; make sense....
gt;
gt; I have two worksheets - worksheet A and worksheet B.
gt;
gt; Worksheet 1 contains a date as text (ddmmyy) eg 080306 in column A
gt; Worksheet 2 contains a text value in column B (either quot;Cquot; or quot;Gquot;)
gt;
gt; Worksheet 2 contains a date as text in column A
gt; Worksheet 2 contains a formula in column B
gt;
gt; The formula on worksheet B, should count the number of C's appearing on
gt; worksheet 1, if the dates match.
gt;
gt; This is what I came up with for the formula on worksheet 2 -
gt;
gt; =IF(A:A='Worksheet 1'A:A, COUNTIF('Worksheet 1'J:J,quot;Cquot;),0)
gt;
gt; When I leave the cell range open (eg A:A) - the formula will count ALL
gt; of the cells containing quot;Cquot; - not just the ones where the date
gt; matches.
gt;
gt; Have I missed something obvious here? Or do I need to use a more
gt; complex formula?
gt;
gt; Thank you
gt;
gt; Rob
gt;
gt;
gt; --
gt; systemx
gt; ------------------------------------------------------------------------
gt; systemx's Profile:
www.excelforum.com/member.php...oamp;userid=29254
gt; View this thread: www.excelforum.com/showthread...hreadid=519644
gt;

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 --
systemx
------------------------------------------------------------------------
systemx's Profile: www.excelforum.com/member.php...oamp;userid=29254
View this thread: www.excelforum.com/showthread...hreadid=519644Best place to look is at www.decisionmodels.com/calcsecrets.htm

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;systemxquot; gt; wrote in
message ...
gt;
gt; Thank you both for your help!
gt;
gt; The formula both worked perfectly once applied - I really appreciate
gt; this.
gt;
gt; If I could be so cheeky to ask one more question - being new to the
gt; world of arrays - I have populated the formula down around 250 cells
gt; across a range of 10 columns (the example I gave was just a cut back
gt; version of what was required).
gt;
gt; Performance with excel drops significantly when using arrays and the
gt; file size also seems so increase dramatically.
gt;
gt; Does anyone know of any information/tutorials relating to arrays and
gt; how to use them and maintain performance levels? Or can anyone provide
gt; any tips based on experience?
gt;
gt; Thanks again!
gt;
gt; Rob
gt;
gt;
gt; --
gt; systemx
gt; ------------------------------------------------------------------------
gt; systemx's Profile:
www.excelforum.com/member.php...oamp;userid=29254
gt; View this thread: www.excelforum.com/showthread...hreadid=519644
gt;