Count values either vba or formula

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




0
10/3/2007 12:40:06 AM
excel 39879 articles. 2 followers. Follow

3 Replies
629 Views

Similar Articles

[PageSpeed] 2

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.

-- 
Biff
Microsoft Excel MVP


"casey" <no_spam@no_spam.com> wrote in message 
news:_oOdnUGtxOkJeZ_anZ2dnUVZ_rGrnZ2d@rcn.net...
> 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
>
>
>
> 


0
biffinpitt (3172)
10/3/2007 1:49:49 AM
On 3 Oct, 01:40, "casey" <no_spam@no_spam.com> wrote:
> 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

0
adam.vero (6)
10/3/2007 4:56:25 PM
On Oct 4, 2:56 am, adam.v...@gmail.com wrote:
> On 3 Oct, 01:40, "casey" <no_spam@no_spam.com> wrote:
>
>
>
>
>
> > 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- 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!

0
troy3182 (5)
10/3/2007 11:26:28 PM
Reply:

Similar Artilces:

Formula to auto-assign a number
I have a spreadsheet that lists parts and associated quantities. The user will be able to add and delete parts from the list. I am looking for a formula that will automatically assign a “dash number” to the parts. The dash numbers will be in numerical order and skip any part that has a quantity of 0. For example: Original Part Qty Automatically assigned “dash number” 3 -1 1 -2 0 0 2 -3 The user may change the quantities on the list therefore I need to formula to automatically recalculate the “dash number” based on the user inputs. For example: Revised Part Qty Automatical...

Help with VBA code please
Greetings, I am stumped so I am hoping the experts can help. I have spent many hours trying to write code to accomplish a certain task and no matter how I tweak it I keep running into problems. I will attempt to explain my goal: I have a worksheet that contains two columns of numbers (with no empty cells in the columns) that need to be compared. It will not always be the same two columns, so I need to be able to input which two before the program runs. The number of rows will also vary. For the sake of simplicity let's call these columns dataOld and dataNew. First I need to search ...

VBA
Hi all! Fisrt of all, I want to thank you for your support :) My question is: How can I get the path of the current Excel file in VBA? thanks --- Message posted from http://www.ExcelForum.com/ Sub CurrDir() Dim MyPath As String MyPath = CurDir MsgBox MyPath End Sub HTH Jason Atlanta, GA >-----Original Message----- >Hi all! > >Fisrt of all, I want to thank you for your support :) > >My question is: How can I get the path of the current Excel file in >VBA? > >thanks > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Try so...

Paste Values not working in Excel 2003 (?)
Hi, Using Excel 2003 with Win xp Pro I copy a range of cels, then I choose EDIT > PASTE SPECIAL > VALUES and it does NOT paste ALL the cel contentent... It does paste all the cels, but only the first 3 lines of each cel's content. Is there any limitation in the number of characters ? Thanks ALex This is not the exact problem you describe, put perhaps it's related: XL2000: "255 Characters in Cell" Error When Copying Worksheet, at: http://support.microsoft.com/default.aspx?scid=kb;en-us;213548 -- HTH - -Frank Isaacs Dolphin Technology Corp. http://vbapro.com ...

Formula for Telecom calculations? Time/money
Hello. I am trying to figure out Excel, and have not used it much. What I need to do is put in a money amount, lets say $5 and the rate per minute is $0.025 (2 1/2 cents) a minute. I need the total minutes available with the total amount of money entered at the rate listed. My formulas are all coming out with incorrect results. Thanks GB enter 5 in A1 enter 0.025 in B1 enter =A1/B1 in C1 the result is 200 in C1 all cells are formatted as general Bill K Greetings from New Zealand "GeekBoy" <nerd_revenge@nerdythree.net> wrote in message news:AD70FF79-B825-4A7D-B452...

Message Count under Inbox
Dear All, Would there be any ways that we can issue a command line call to our Exchange 2003 Server and find out the number of messages currently stored under the Inbox? If there is, could you let me know how please? Any help will muchly be appreciated. Thanks and Regards Andy ...

Reference Grouped Objects Using VBA
Hello I have grouped four charts, three text boxes (from the control toolbox) and a rectangle. Prior to grouping, the code I had to update the axes on both charts and text for the text boxes worked fine. After grouping the objects the code fails. How do you reference a specific object contained in a group? If Shp is a grouped shape then Shp.GroupItems(2) references the second shape in the group -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware .. "BJ" <bradley.john@westrac.com.au> wrote in message Hello I have grouped four charts, three text ...

Counting coloured cells #3
I have a list of percentages that are conditionally formatted and I want to be able to count how many cells are each different colour. I don't think you can do this with count if as it only looks for numbers or text and not formatting but essentially I want to count the number of cells in a range which are red/gold or green. Use the following UDF Function CountColor(rng As Range, colorRng As Range) For Each cl In rng If cl.Interior.ColorIndex = colorRng.Interior.ColorIndex Then ' use this for background color CountColor = CountColor + 1 End If ...

Formulae for customized field
Dear sir , I want to use the formulae iif (and ( expression1,expression2),truepart,falsepart). But its not working it is sho wing syntex error with And. how to solve the problem? -- Amey B. Vidvans, Planning Engineer Vidvans_amey@rediffmail.com It's almost like EXCEL but not quite. iif (expression1 AND expression2,truepart,falsepart) Watch out for quoted literal text, eg "truepart" for text and flag fields. -- Trevor Rabey 0407213955 61 8 92727485 PERFECT PROJECT PLANNING www.perfectproject.com.au "Amey" <Amey@discussions.microsoft.com...

VBA Event Procedures won't run in Access 2007
For some strange reason none of my Event Procedures are running in my access database. I can use macros for events just fine, but if I want to use a VBA event procedure nothing happens. If I put breakpoints in at any event in the VBA code, I never see a single one come up. What is going on here? Do I have to enable VBA somewhere in Access 2007? I'm really pulling my hair out with this one. Thanks for any help! -mindseye11 See this page for a nice explanation: http://www.access.qbuilt.com/html/trust_center.html -- Jeff Conrad - Access Junkie - MVP Alumni SDET - XAS Services - Microsoft...

Joining 3 Queries That Count
I am trying to decipher the “status” of records in my table based on two date fields and the name of the agent assigned to each record. The fields are as follows: SUPPORT MGR (Name) DC (Date Completed) Follow-up Date I have 3 separate queries based on these fields that work as follows: a) Completed SELECT [Master Table].[SUPPORT MGR], Count([Master Table].[MERCHANT ID]) AS [# COMPLETED] FROM [Master Table] WHERE ((([Master Table].DC) Between [Start Date] And [End Date])) GROUP BY [Master Table].[SUPPORT MGR]; b) In Follow-Up SELECT [Master Table].[SUPPORT MGR], Count(...

Which formula should I use.....
Hi there, I'm building a contract, and I need specific cells from each page in the workbook to total up on a pricing sheet (still in workbook). I've tried using IF commands to build this mammoth equation, it just isn't working. I'm getting quite frustrated, I've been working on this for a few weeks. I'm new to such detailed formulas in Excel, and any help anyone could give me would be greatly appreciated!! Thank you!!!! Heather -- HeatherCox ------------------------------------------------------------------------ HeatherCox's Profile: http://www.excelforu...

Omit points from plot if cell is formula returns a non-numeric res
How can I prevent cells with a non-numeric function result from being plotted as zero values. The function =IF(G3>0,F5+G3,"") Plots values as it should for G>0 but when G<=0 it plots points on the x-axis instead of omiting the points as it does when the cells are blank. On the spread sheet the cells display blank as they should. Hi, Charts plot all non numeric values as zero. There will however interpolate values if #N/A is used. =IF(G3>0,F5+G3,NA()) Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Dave" <Dave@discuss...

How to get formulas starting from the referenced cells
Hi all, I've cells on a sheet which are referenced by formulas which are on different sheets (about 20) on the same workbook, and I'm looking for a way to get all these formulas. I've tried: 1) The Trace command, but I see only a table icon every time I've this type of reference 2) The Find command, but there are cell references wrote in different manners (ie B2, $B2, B$2, $B$2), and formulas which refer to results of other formulas that refer to the cells, so it's not so simple to trace all the dependencies Any help is appreciated Thanks Dario I'm not sure I full...

how to count number of cells in a color ?
I have a list of data in column, they have conditioanal format to different colors, e.g. blue, red, brown. I want to count the total number of blue. How to do it ? The "CountIf" function does seem to help. Any suggestions ? Thanks. Hi Francisco See http://www.cpearson.com/excel/CFColors.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Francisco" <Francisco@discussions.microsoft.com> wrote in message news:927304A5-05ED-425E-A5F5-AE981BA76DC7@microsoft.com... >I have a list of data in column, they have conditioanal format to different > colors,...

Assign Values to String Value
Hello, I am trying to assign a numeric value to cells with string/text variables. For instance, 10-2 would signify 4 hours... so the value I would like to calculate would be 4. Is there an easy way to do this? Thanks "PC-Renew" wrote: > I am trying to assign a numeric value to cells with string/text variables. > For instance, 10-2 would signify 4 hours... so the value I would like to > calculate would be 4. Is there an easy way to do this? VLOOKUP would be, imo, the easiest. Eg: Assume textstring in A1: 11-7 Then in B1: =VLOOKUP(A1,{"10-2",4;"1...

Last Value in a Column when Value <> 0
Hi, can someone provide me a formula that populates a cell with the last value in a column that does not = 0? Below is an example 3 6 7 0 0 0 The goal is to populate a cell with the value of 7. I am currently using the below formula that populates the last value of a column: =INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement Total'!B:B)) I do not however know how to change this to not populate the last value when it is zero. Your help is appreciated. Assuming you want the last *numeric* value that <>0. Try something like this: =LOOKUP(1E100,1/'...

how to find the operating syetem year in vba
Hello, i need to find out what the o.s. year is in vba so that i can delete tab on a sheet. my sheet has several tab, some which i don't want to delete like "ledger" "2008" and the current year "2009" if i add extra years i would like to be able to delete years "2010" + i have some code wich add the years to my sheet which basicaly copys previous years and clears all the contents and renames it to the next year, in doing so it all so adds various totals to my "ledger" sheet. For Each sh In Sheets If sh.Name = "Ledger&q...

Formula help please #4
I want to countif column C is 2-Aug-04 & Column D is tp015& Column E is 23. I need to capture this all in the same function in order to shorten time for data entry, and it allows me to get the speciic data needed ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Hi try =SUMPRODUCT(--(C1:C100=DATE(2004,8,2)),--(D1:D100="tp015"),--(E1:E100=2 3)) For more about Sumproduct see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &quo...

Formula / Macro Help
Hi, Can anyone help me? I have some text in cell A1 and i wish it to search in column A on other worksheets for that reference. If it then finds a match on one o the sheets i want it to tell me on sheet1 in B1 that its in worksheet 4 for example ie sheet1 A1 = Toy Car sheet4 A335 = Toy Car sheet1 B1 = Sheet4 Can this be done?? Any help appreciated Regards Crai -- BSLAUTOMATIO ----------------------------------------------------------------------- BSLAUTOMATION's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=761 View this thread: http://www.excelfo...

Recalculate the Formula
Earlier today I was given 2 formulas for a problem I was having. I started with a formula that worked and I wanted to add 2 conditions to it. The formula was: =IF(OR(‘Source Sheet’!C10={“”,”C”, “B”}),’Source Sheet’!B10). I wanted to add a condition that would allow if C10=”S” for any number that I entered in the source sheet B10 to be entered in the destination sheet B10. The second condition would be if “D” was entered in the source sheet a 2 and only 2 would be entered in the destination sheet B10. There is no number in the B10 of the source sheet for “D”, and that is the...

Simple but difficult formula
=(K3-(K3/B3))/3.79 K3 = Tank Size B3 = Actual g/l concentration 3.79 converting liter to gals This formula works fine as long as the tank size is 100 and the g/l is 1 But when I use a different size tank and the g/l is other than 1, it will not calculate correct. Why? It's easy to calculate how many grams to ADD if it's below my recommended number of grams per liter. But if the g/l is over the recommended then I need to remove x amount of water from tank to return it to the recommended g/l. Thanks in advance!!! Hi Richard "Richard" <bobdelrn@yahoo.com> wrote in messag...

Copy a value without linking?
I am trying to automatically set the value of a text box within a subform when the user creates a new record in that subform. I want the value to be pulled from another form (the main form which the subform is linked to) in the database, but I do not want the value to ever be updated. In other words, I want to just copy the value from the main from to the test box on the subform-- once and only once. Example: MainForm: field="Name" (field is a combo box) Subform: field="NameAtTimeRecordWasCreated" (field is a text box) The "Name" field on MainForm...

I need a gap when the result of a formula is blank or zero
Hello everyone, I have a line chart that plots the evolution of an economic indicator over the months. Each of the 12 cells plotted (the source range) has a formula. It fetches a value from a dabase. For all the months to come, the formula shows "" (like empty cell) or a blank (" "). I might use zero, were it needed. But what I need is that beyond the current month, the line makes a gap (no dots) instead of treating formulas as zeroes. Are there any parameters or options that could help me? Thank you Vicente Soler Hi, Use NA() instead of zero or "". Charts ...

How do I code the cloning of an object in vba?
Specifically a frame containing both a textbox control and a spinbutton control. Thanks Steve Can you be more specific? What are you wanting to do? Are you just wanting to copy a control to another userform? -- Cheers, Ryan "sarndt" wrote: > Specifically a frame containing both a textbox control and a spinbutton > control. > > Thanks > > Steve ...