Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formulas
In my spreadsheet I have an input table. I want to count the number of trucks
leaving each city on a certain day. Then in addition, ideally for one city I want to know how many loads are heavier that 15MT. The other cities don't have heavy loads. Can anyone please help with a formula to use. I think it needs to be an array formula. Input Table Output Table <15 15 Date City Weight Date Omsk Tobolsk Tobolsk 11-Feb Omsk 12 11-Feb 0 0 0 12-Feb Omsk 3 12-Feb 2 1 12-Feb Tobolsk 5 13-Feb 2 0 0 13-Feb Omsk 12 14-Feb 2 1 1 13-Feb Omsk 12 15-Feb 0 0 1 14-Feb Omsk 4 16-Feb 1 1 1 14-Feb Omsk 1 17-Feb 1 0 0 14-Feb Tobolsk 25 18-Feb 0 0 0 14-Feb Tobolsk 12 15-Feb Tobolsk 20 16-Feb Omsk 3 16-Feb Tobolsk 5 16-Feb Tobolsk 24 17-Feb Omsk 5 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formulas
=COUNTIF(B:B,"Omsk")
=SUMPRODUCT(--(B2:B200="Omsk"),--(C2:C200<15)) SUMPRODUCT cannot use whole column references -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Peledon" wrote in message ... In my spreadsheet I have an input table. I want to count the number of trucks leaving each city on a certain day. Then in addition, ideally for one city I want to know how many loads are heavier that 15MT. The other cities don't have heavy loads. Can anyone please help with a formula to use. I think it needs to be an array formula. Input Table Output Table <15 15 Date City Weight Date Omsk Tobolsk Tobolsk 11-Feb Omsk 12 11-Feb 0 0 0 12-Feb Omsk 3 12-Feb 2 1 12-Feb Tobolsk 5 13-Feb 2 0 0 13-Feb Omsk 12 14-Feb 2 1 1 13-Feb Omsk 12 15-Feb 0 0 1 14-Feb Omsk 4 16-Feb 1 1 1 14-Feb Omsk 1 17-Feb 1 0 0 14-Feb Tobolsk 25 18-Feb 0 0 0 14-Feb Tobolsk 12 15-Feb Tobolsk 20 16-Feb Omsk 3 16-Feb Tobolsk 5 16-Feb Tobolsk 24 17-Feb Omsk 5 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formulas
I didn't get this to work out what was required unforunately. I don't want
to multiply. However I have managed to work everything out by using a pivot table. Thank you for your time. "Bob Phillips" wrote: =COUNTIF(B:B,"Omsk") =SUMPRODUCT(--(B2:B200="Omsk"),--(C2:C200<15)) SUMPRODUCT cannot use whole column references -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Peledon" wrote in message ... In my spreadsheet I have an input table. I want to count the number of trucks leaving each city on a certain day. Then in addition, ideally for one city I want to know how many loads are heavier that 15MT. The other cities don't have heavy loads. Can anyone please help with a formula to use. I think it needs to be an array formula. Input Table Output Table <15 15 Date City Weight Date Omsk Tobolsk Tobolsk 11-Feb Omsk 12 11-Feb 0 0 0 12-Feb Omsk 3 12-Feb 2 1 12-Feb Tobolsk 5 13-Feb 2 0 0 13-Feb Omsk 12 14-Feb 2 1 1 13-Feb Omsk 12 15-Feb 0 0 1 14-Feb Omsk 4 16-Feb 1 1 1 14-Feb Omsk 1 17-Feb 1 0 0 14-Feb Tobolsk 25 18-Feb 0 0 0 14-Feb Tobolsk 12 15-Feb Tobolsk 20 16-Feb Omsk 3 16-Feb Tobolsk 5 16-Feb Tobolsk 24 17-Feb Omsk 5 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formulas
It isn't multiplying, it is counting.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Peledon" wrote in message ... I didn't get this to work out what was required unforunately. I don't want to multiply. However I have managed to work everything out by using a pivot table. Thank you for your time. "Bob Phillips" wrote: =COUNTIF(B:B,"Omsk") =SUMPRODUCT(--(B2:B200="Omsk"),--(C2:C200<15)) SUMPRODUCT cannot use whole column references -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Peledon" wrote in message ... In my spreadsheet I have an input table. I want to count the number of trucks leaving each city on a certain day. Then in addition, ideally for one city I want to know how many loads are heavier that 15MT. The other cities don't have heavy loads. Can anyone please help with a formula to use. I think it needs to be an array formula. Input Table Output Table <15 15 Date City Weight Date Omsk Tobolsk Tobolsk 11-Feb Omsk 12 11-Feb 0 0 0 12-Feb Omsk 3 12-Feb 2 1 12-Feb Tobolsk 5 13-Feb 2 0 0 13-Feb Omsk 12 14-Feb 2 1 1 13-Feb Omsk 12 15-Feb 0 0 1 14-Feb Omsk 4 16-Feb 1 1 1 14-Feb Omsk 1 17-Feb 1 0 0 14-Feb Tobolsk 25 18-Feb 0 0 0 14-Feb Tobolsk 12 15-Feb Tobolsk 20 16-Feb Omsk 3 16-Feb Tobolsk 5 16-Feb Tobolsk 24 17-Feb Omsk 5 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formulas
SUMPRODUCT can be used to do more than multiply, when one evaluates
conditionals in ranges as Bob did. However, you'd have to add an additional term to Bob's formula for the date to get what you asked for. That might be difficult given your data layout. In article , Peledon wrote: I didn't get this to work out what was required unforunately. I don't want to multiply. However I have managed to work everything out by using a pivot table. Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formulas | Excel Worksheet Functions | |||
IF statement in Array Formula's | Excel Discussion (Misc queries) | |||
array formulas disabled? | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions |