Excel - If, Count & Array

Asked By jun

23-Apr-08 12:28 AM

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)

23-Apr-08 12:44 AM

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

23-Apr-08 12:45 AM

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