If, Count & Array - jun

All

I have the following scenario

ADL	PC	NOTEBOOK	IN-USE
MEL	LCD	17 INCH	                SPARE
MEL	PC	NOTEBOOK	IN-USE
MEL	PC	NOTEBOOK	IN-USE
SYD	PC	DESKTOP	                SPARE
SYD	PC	NOTEBOOK	IN-USE

I need to count how many in use, pc, notebooks, in syd.  I'm thinking I
would use If, count and then an array to look over the range.  I would like
it in either vba (just learning) or the easiest way to write the formula

I hope I have put enough details and explained myself
TIA
Bec
(it's grey and raining in Sydney, Aus)

Bec --Try - pdberge

Bec --
Try this:

=SUMPRODUCT(--(A1:A100="SYD"),--(B1:B100="PC"),--(C1:C100="NOTEBOOK IN-USE"))

Set the range to the right number of rows.

HTH

Assuming you have 4 columns of data - T. Valko

Assuming you have 4 columns of data there:


=SUMPRODUCT(--(A1:A6="syd"),--(B1:B6="PC"),--(C1:C6="notebook"),--(D1:D6="in-use"))

Better if you use cells to hold the criteria:

J1 = syd
K1 = PC
L1 = notebook
M1 = in-use

=SUMPRODUCT(--(A1:A6=J1),--(B1:B6=K1),--(C1:C6=L1),--(D1:D6=M1))


--
Biff
Microsoft Excel MVP