Excel, Count items on Sheet 3 that match criteria on Sheet 4 1

[srogers]

Hi -
(Using Excel2000) I am trying to count the records in the field "Acct" on worksheet 3 that match this criteria on worksheet 4:
1. $J17 on sheet 4 = value in "Acct" field on sheet 3
2. $C17 on sheet 4 = value in "Store" field on sheet 3
3. $I$10 on sheet 4 = value in "Status" field on sheet 3

Any suggestions on how to do this?
Can I only use 3D references on Count or CountA? (from what I read it didn't look like I could use the sheet# reference with DCount).

Thanks for any help you can provide. I am spinning my wheels but not making progress.
srogers

 

[lespaul]

try using named ranges, then you can reference the name from anywhere in the workbook without first referencing the worksheet. Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Dreamboat]

See if this helps you:

http://www.cpearson.com/excel/array.htm

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros

 

[xlbo]

1. $J17 on sheet 4 = value in "Acct" field on sheet 3
2. $C17 on sheet 4 = value in "Store" field on sheet 3
3. $I$10 on sheet 4 = value in "Status" field on sheet 3

Lets say Acct field is col A
Store field is col B
Status field is col G

enter:
=sum((sheet3!A2:A1000=J17)*(sheet3!B2:B1000=C17)*(sheet3!G2:G1000=I10))
enter this with CTRL+SHIFT+ENTER as opposed to just ENTER. You will see { } round the formula - this indicates it is an ARRAY formula Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[srogers]

Geoff, Thank you SO much for that array you provided - and the tip on Ctrl, Shift, Enter. So far it looks to be just what I needed!!

Anne, after reading the web page you sent I was afraid I wouldn't be able to use it because I misunderstood this part: When you use more than one range in an array formula, all of the ranges must contain the same number of elements. Otherwise, an error is returned.
I was afraid it was including the items in the comparison.
After seeing Geoff's post I realized that wasn't the case.

Leslie, thank you for the named ranges tip!

I'm working two jobs right now (therefore the delay in replying) and ya'll really helped make my day. I really had "Count" on the brain and wouldn't have thought of using Sum in an array.
Thanks,
Sharon