View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default If, Count & Array

Assuming you have 4 columns of data the

| ADL | PC | NOTEBOOK | IN-USE |

=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


"Bec" wrote in message
...
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)