#### formula needed #4

```Thanks for the help. I like that way a lot better. I actually got a
formula to work, and it probably follows along with the suggested
earlier. It is written with the names of the tabs I had to use, but
maybe someone else will be able to use it. Again, thanks to everyone.

=SUMPRODUCT(--('Call Frequency'!\$A\$1:\$A\$50000<>"")/COUNTIF('Call
Frequency'!\$A\$1:\$A\$50000,'Call Frequency'!\$A\$1:\$A\$50000&""),--('Call
Frequency'!\$C\$1:\$C\$50000='Repeat Model Type Breakdown'!A2))

Gary's Student Wrote:
> You can accomplish your counting without any formulae.
>
> Step1 - since you can't change Sheet1, copy it into another sheet(say
> Sheet3)
>
> Step2 - in Sheet3, delete column B (it is useless)
>
> Step3 - in Sheet3 insert a header row at the very top of the sheet and
> put
> labels ontop of your two columns (Customer and Product)
>
> Step 4- in Sheet 3 select your two columns and
> Data - Pivot Table -  Next - Next -Layout
>
> then drag Product into the Row section of the template and drag
> Customer
> into the Data area of the template
>
> OK - Finish
>
> The resulting Pivot Table should give you exactly what you want.
> --
> Gary's Student
>
>
> "Darrell" wrote:
> -
>
> Hope I can clarify. Here is an example of what I'm working with.
> Sheet
> One is Raw Data dumped in from a seperate report. Sheet 2 is where
> I'm
> doing all my work. Sheet One can not be changed around because it is
> used by other calculations. Also, Sheet One has 3 columns where
> column
> B is unneeded information. No Cell will actually contain a number. It
> is all Text except for the desired result.
> SHEET 1
> Customer 1 	Product 1
> 		Product 1
> 		Product 1
> Customer 2 	Product 2
> 		Product 2
> 		Product 2
> Customer 3 	Product 1
> 		Product 1
> 		Product 1
> SHEET 2
> Product 1    =2
> Product 2    =1
> Product 3    =
>
>
> I want the formula, for example, to search for Product 1 (to count
> the
> number of customers using a specific product) on sheet One. When
> seeing
> product 1 in Column C, I want it to check Column A for text. If A is
> populated, then count, so the result for this example would be
> Product
> 1 = 2. The products are listed on Sheet 2 where the result is to be
> posted.
>
> JulieD Wrote:-
> Hi Darrell
>
> i'm going to assume you meant, count the number in column A of
> sheet1
> as
> long as the value in column C of sheet 1 is not blank
> formula on Sheet2 is (where the value you're looking up is in cell
> A2)
>
> =SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$100=A2),--(Sheet1!\$C\$1:\$C\$100""))
> check out http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> for details on the sumproduct function
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Darrell" Darrell.1o9j8b@news.excelbanter.com wrote in message
> news:Darrell.1o9j8b@news.excelbanter.com...-
>
> Hello,
> I hope I can explaine this well enough. I am using two worksheets.
> Worksheet 1 is an imported file, worksheet 2 is where all my
> calculations are getting done. Sheet 1 column A  and column C are
> the
> only two columns I need to look at. Sheet 2 has the values of what I
> am
> looking for in sheet 1. All data is also alpha characters. No
> numbers.
>
> I need a formual to:
>
> find the value from sheet 2. The value will be repeated several
> times
> on sheet 1. When finding value, count cell in column A as long as
> long
> as it is not blank.
>
>
>
> --
> Darrell--
>
>
> --
> Darrell
> -

--
Darrell
```
 0
5/5/2005 9:25:15 PM
excel.newusers 15348 articles. 2 followers.

0 Replies
386 Views

Similar Articles

[PageSpeed] 4

Similar Artilces:

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =\$B\$2*3 becomes =sheet1!\$B\$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's \$B\$2 (equivalent of sheet2!\$B\$2). I have tried entering =!\$B\$2*3 which works initially but recalculates using the \$B\$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Help #4
When you go into, Sales Batch Entry, select the batch, transactions, lookup by document number, the view I am getting now is green and white. I am seeing all the documents in all the batches. I don't want this view, I want to be able to view only what is in the batch selected. What controls this feature? rcr, what you can do is Go to Sales Transaction Entry, Enter your Batch ID, then if you see the lookup, it'll only have the transactions entered for that Batch alone. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "rcr" wrote: > When you go...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Most unusual question
Using Outlook Office 2003 and this evening, everytime I try to open a link from email in Outlook, it opens up My Documents! What's wrong and how do I correct this problem! Thanks! ...

Need VB5 code for these VB6 instructions
I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. Need Vb5 code for: Private WithEvents poSendmail as vbSendMail.clsSendmail Private Sub Form_Load() Sdet poSendMail = New clsSendMail End Sub Help!! On Tue, 26 Jan 2010 13:33:01 -0800, Dennis Rose <DennisRose@discussions.microsoft.com> wrote: >I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. >Need Vb5 code for: > >Private WithEvents poSendmail as vbSendMail.clsSendmail > >Private Sub Form_Load() > Sdet poSendMail = New clsSendMail >...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

validation list #4
hi i have created a validation list with more then 500 name, but when i try to find a particual name i have to go through all of them . is it possblie to write the first alphabitic letter in the list and the drow down list will point to that particual name e.g if the name in the list is Mark S. Mark can i write only Ma to find all Names start with Ma You can do this if you use a combobox from the control toolbox instead of DV. -- HTH Bob Phillips "osaka78" <osaka78@discussions.microsoft.com> wrote in message news:12E9A3FF-5773-4002-8BCD-C2498BDE9EB0@microsoft.com......

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

No return on basic search of activities, CRM 4.0
If I go into activities and view all of my activities I get the full list of phone calls and emails. However if I try to search for an activity that I've carried out with a contact i.e. John Smith, The Company. I get no results returned if I search on either term, even though an activity on this account is in the list. If I go to Advanced Find, I can search for John Smith and/or The Company and the contact is then returned in the search. Why is the basic search not working? (I'm using the web client) Thanks in advance! Hi, Have you configured your required columns to be ...

sum if formula
=SUM(IF('Day 1'!\$C\$4:\$C\$37=A22,'Day 1'!\$D\$4:\$M\$37))+SUM(IF('Day 2'!\$C\$4:\$C\$37=A22,'Day 2'!\$D\$4:\$M\$37))+SUM(IF('Day 3'!\$C\$4:\$C\$37=A22,'Day 3'!\$D\$4:\$M\$37))+SUM(IF('Day 4'!\$C\$4:\$C\$37=A22,'Day 4'!\$D\$4:\$M\$37))+SUM(IF('Day 5'!\$C\$4:\$C\$37=A22,'Day 5'!\$D\$4:\$M\$37))+SUM(IF('Day 6'!\$C\$4:\$C\$37=A22,'Day 6'!\$D\$4:\$M\$37))+SUM(IF('Day 7'!\$C\$4:\$C\$37=A22,'Day 7'!\$D\$4:\$M\$37))+SUM(IF('Day 8'!\$C\$4:\$C\$37=A22,'Day 8'!\$D\$4:\$M\$37))+SUM(IF('Day 9'!\$C\$4:\$C\$37=A22,'Day ...

Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that made it disappear. If I go to the “View”menu, Formula Bar has a check next to it, as if it should be showing, but I can’t see it. I tried checking and un-checking, but no where to be seen. I have two other tool bars and my status bar, that are working fine. Could anyone give me a hand with this? Thanks, I am currently using Office v. X. You may have switched "Full screen" on. To uncheck, see the View menu -- Kind Regards, Niek Otten Microsoft MVP - Excel "JorgeH" <JorgeH@d...

Need Help #5
I had installed outlook on Exchange 2003. Now when I try to republish Default global address list, I get the error "MAPI or an unspecified service provider". I think my MAPI profile must be currupted. Can anyone tell me how to fix this problem? Thank Kumar Check the version of mapi32.dll in your system32 directory. You may simply need to replace it with the mapi32.dll version that is found in the /exchsrvr/bin folder. Oh, and uninstall Outlook please. There is a reason that installing Outlook on an Exchange server isn't recommended and isn't supported.... -- Ben W...

I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

Need to Hire Outlook 2003 Help
Hi I'd like to hire someone that can walk me through some specific stuff with outlook 2003 I'm using the Lotus Notes to Outlook plugin My compnay is a Notes shop - which means I get NO support for outlook --- this plugin is a god send and i want to make sure I get it set up correctly It's coming out of my pocket but I'm willing to pay if I can find the right person to help me Please post ideas here or email me Thanks Brendan ...

transposing cells through cut command
Hello, I have a worksheet in which cells are arranged with headers in Column1. I need to arrange them to be in Row1. The problem is that I have other worksheets linked to this worksheet, so if I use a copy command, those links would still refer to a previous range and not to the "copied range". Does anyone know how to deal with this problem? I have seen replies below to similar problems, but I don't believe that mine will be resolved through solution below, because formulas in other worksheets would still be linked to Sheet1 and not to the sheet into which I pasted the formul...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

I make Outlook 2000 use a Outlook.pst file on a separate hard disk instead of the usual C:\Documents & Settings\ folder. Everything is fine. I also TICK "Leave copy of message on server" so I always have a backup copy. Now when i format my PC and reinstall Windows & Outlook, I point to my D:\Outlook.pst and it starts using it. Then all of a sudden it starts downloading ALL my emails again. If I format my PC once again and reinstall Windows & Outlook, then Outlook starts downloading ALL my emails TWICE (there's 2 copies of each email downloaded, plus the copy I...

I need to shift cells down only to a point w/o disturbing the bot.
I have a spreadsheet in which the cells need to be moved down, however only to a point. If I move everything down it disturbs the bottom row of totals. Can anyone help me? I think you need to provide a few more details about what exactly you are trying to accomplish. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "wade2753" <wade2753@discussions.microsoft.com> wrote in message news:D905FEE5-2EA7-47A1-A532-4D0CA743121C@microsoft.com... >I have a spreadsheet in which the cells need to be moved down, >however o...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

i want to mail merge 1 address per postcard 4 to a page,
I have postcards preprinted on one side. I have made a publication using mail merge to add addresses. But I have 4 postcards on a page when I do the mail merge I am getting 4 postcards with the same address. How do I get one address per card but do 4 to a page? What version Publisher? In some earlier versions the print preview showed all the addresses the same, it was/is a Publisher bug. In Publisher 2007, once you have your postcard created --- File, print merge, on this screen, select multiple copies per sheet and landscape. There is a print preview on this screen. -- Mary Sauer ...

Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) wit the column range (E6:E35). Is it possible to include a multiple sheet count and column range i the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to rea multiple sheets.. Please help on this -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=...

Outlook Profiles #4
Does anyone know how to auto-configure Outlook profiles (exchange server, alias, .pst, etc) at logon based on username??? Create a prf-file with the variable %username%. Instructions on the prf-file can be found in the Office Resource Kit -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Save Embedded Pictures in Their Original Format -Create an Office XP CD slipstreamed with Service Pack 3 ----- "MessageQuestor" <crayola5150@excite.com> wrote in message news:05790...

Need to automaticallu input date in form
This seems to be a very popular question posed on the forum, and I have read through and tried the most viable solutions given to others. I have three diferent forms that update a table with three different date fields. Although I have set the default value for all the date fields in the table to =Date()- 1, this only works on one of the forms, even though that field in each form is given same properties. The forms are used to update an inventory table containing part numbers, wip dates and amounts, packing dates and amounts, and shipping dates and amounts. I use the three different forms bec...

Need help in data copying. #3