Count values either vba or formula

[casey]

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

 

[T. Valko]

Try this:

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

Copy down as needed.

 

[adam.vero]

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

Sounds like a perfect use for a Pivot Table to me

 

[troy]

Sounds like a perfect use for a Pivot Table to me- Hide quoted text -

- 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!