Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count array with OR condition | Excel Worksheet Functions | |||
COUNT and IF Array | Excel Worksheet Functions | |||
Count & Array Function | Excel Worksheet Functions | |||
Count If Array Formula | Excel Worksheet Functions | |||
Count if array contains cells of a certain value | Excel Worksheet Functions |