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

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

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

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

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.