If, Count & Array
 

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)

If, Count & Array
 

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

"Bec" wrote:

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)

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)