I have a group of stores operating in different cities and counties within a
state and need a function to find the total sales for each respective city
and county. Column B (only unique column) is Dealer Name, C is City name, D
is city code, F is County Name, G is County Code, and I is Store Sales.
The city/county codes are in the format of "00-00" where the first set of
numbers denotes the county and the second set the city. For example, the
city of Conway, which is in Faulkner County is denoted by "23-01" whereas the
city of Mayflower in the same county is denoted by "23-02."

I've tried several IF functions but nothing that has really made any
headway. Anyone have a suggestion?

Assuming C1 as variable when you type the name of the city to see the total;
=sumproduct(--(c2:c1000=c1),(i2:i1000))

For the country use
=sumproduct(--(f2:f1000=f1),(i2:i1000))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"handyaccountant" escreveu:

I have a group of stores operating in different cities and counties within a
state and need a function to find the total sales for each respective city
and county. Column B (only unique column) is Dealer Name, C is City name, D
is city code, F is County Name, G is County Code, and I is Store Sales.
The city/county codes are in the format of "00-00" where the first set of
numbers denotes the county and the second set the city. For example, the
city of Conway, which is in Faulkner County is denoted by "23-01" whereas the
city of Mayflower in the same county is denoted by "23-02."

I've tried several IF functions but nothing that has really made any
headway. Anyone have a suggestion?

Works perfectly and will save me lots of hours. Thanks!

"Marcelo" wrote:


Assuming C1 as variable when you type the name of the city to see the total;
=sumproduct(--(c2:c1000=c1),(i2:i1000))

For the country use
=sumproduct(--(f2:f1000=f1),(i2:i1000))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"handyaccountant" escreveu:

I have a group of stores operating in different cities and counties within a
state and need a function to find the total sales for each respective city
and county. Column B (only unique column) is Dealer Name, C is City name, D
is city code, F is County Name, G is County Code, and I is Store Sales.
The city/county codes are in the format of "00-00" where the first set of
numbers denotes the county and the second set the city. For example, the
city of Conway, which is in Faulkner County is denoted by "23-01" whereas the
city of Mayflower in the same county is denoted by "23-02."

I've tried several IF functions but nothing that has really made any
headway. Anyone have a suggestion?

Glad to help you thanks for the feedback.
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"handyaccountant" escreveu:

Works perfectly and will save me lots of hours. Thanks!

"Marcelo" wrote:


Assuming C1 as variable when you type the name of the city to see the total;
=sumproduct(--(c2:c1000=c1),(i2:i1000))

For the country use
=sumproduct(--(f2:f1000=f1),(i2:i1000))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"handyaccountant" escreveu:

I have a group of stores operating in different cities and counties within a
state and need a function to find the total sales for each respective city
and county. Column B (only unique column) is Dealer Name, C is City name, D
is city code, F is County Name, G is County Code, and I is Store Sales.
The city/county codes are in the format of "00-00" where the first set of
numbers denotes the county and the second set the city. For example, the
city of Conway, which is in Faulkner County is denoted by "23-01" whereas the
city of Mayflower in the same county is denoted by "23-02."

I've tried several IF functions but nothing that has really made any
headway. Anyone have a suggestion?