Count values either vba or formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

casey - 03 Oct 2007 01:40 GMT

Sample of what I am trying to achieve:

Worksheet1: (column A, B, C, D, E) - source data

id,primary region, secondary region, tertiary region, status
111,americas,new york, NA, increase
111,americas,canada,NA, increase
111,americas,mexico,LATAM,increase
111,americas,peru,LATAM,decrease
112,europe,france,WEST,increase
112,europe,spain,WEST,decrease
112,europe,uk,WEST,decrease
112,europe,portugal,WEST,decrease
etc....

Worksheet2: (contains summary sheet) - summarize data

111,americas,NA, increase, 2 <----- count instances found from worksheet1
111,americas,LATAM,increase, 1
111,americas,LATAM,decrease, 1
112,europe,WEST,increase, 1
112,europe,WEST,increase, 1
112,europe,WEST,decrease, 3

Reply to this Message

T. Valko - 03 Oct 2007 02:49 GMT

Try this:

=SUMPRODUCT(--(Sheet1!A$2:A$9=A2),--(Sheet1!B$2:B$9=B2),--(Sheet1!D$2:D$9=C2),--(Sheet1!E$2:E$9=D2))

Copy down as needed.

Signature

Biff
Microsoft Excel MVP

> Sample of what I am trying to achieve:
>
[quoted text clipped - 19 lines]
> 112,europe,WEST,increase, 1
> 112,europe,WEST,decrease, 3

Reply to this Message

adam.vero@gmail.com - 03 Oct 2007 17:56 GMT

> Sample of what I am trying to achieve:
>
[quoted text clipped - 19 lines]
> 112,europe,WEST,increase, 1
> 112,europe,WEST,decrease, 3

Sounds like a perfect use for a Pivot Table to me

Reply to this Message

troy@loxleycorp.com - 04 Oct 2007 00:26 GMT

On Oct 4, 2:56 am, adam.v...@gmail.com wrote:

> > Sample of what I am trying to achieve:
>
[quoted text clipped - 23 lines]
>
> - Show quoted text -

Hi Casey,

If you create an identifier/key for each summary group you can then
use =COUNTIF() to count them.

Like this:

Worksheet1:
1. In F1 enter =A1&B1&D1&E1 (don't include the secondary region). This
will indicate which summary group the record belongs to.
2. Copy down.
Worksheet2:
3. In E1 enter =COUNTIF(Sheet1!F:F,A1&B1&C1&D1). This searches Column
F on Worksheet1 for the summary group and counts how many it finds.
You could also add a new column with the summary group identifier in
it and reference this cell in your =COUNTIF instead of creating the
identifier directly in the =COUNTIF (does that make sense ?? lol)
4. Copy down.

My usual approach with these situations is to insert a new column
before A on both sheets and put the identifiers in this column, then
hide it. This way your model looks the same as it always did but you
have the mechanics churning away in the background!

HTH

cheers,
t.

www.eXtreme-eXcel.com
...be indispensable... they'll pay you more!

Reply to this Message

Rate this thread: