Help what kind of formula?

I've used Excel formulas in a basic way (sums, averages, divisions, and
multiplication of cell data) for about a year. 

I came across this formula today for a project I need to complete. I've
never seen the "SUMIF" or the dollar symbol, OR the ampersand! After
hilighting the formula to see if I could just figure it out, I'm
stumped.

Any chance someone could explain what function these symbols serve

Here is the formula

=SUMIF($D$3:$D$28,"="&$C38,H$3:H$28) :confused:


-- 
Emil0
------------------------------------------------------------------------
Emil0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31232
View this thread: http://www.excelforum.com/showthread.php?threadid=508999

0
2/6/2006 6:55:06 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
686 Views

Similar Articles

[PageSpeed] 33

The $ symbol is used to make the cell references absolute - if you had
a formula which referred to D3 and copied this down the column, it
would become D4, D5 etc, and if you copied it across the row it would
change to E3, F3 etc. Sometimes you do not want this to happen, so the
$ symbol in front of the row or column reference prevents it from
changing.

The ampersand is used to join text together and is known as the
concatenation operator - it does the same job as the CONCATENATE( )
function. In your example, the equals sign is being added to the
beginning of the contents of C38.

The SUMIF( ) function allows you to add values together if some
condition is met. In your formula, the values to be added are in the
range H3 to H28, and each cell of this range will be summed if the
contents of the corresponding cell in the range D3 to D28 is equal to
the contents of cell C38.

Hope this makes things a bit clearer for you - you can find more
details in Excel Help.

Pete

0
pashurst (2576)
2/6/2006 7:33:06 PM
I took a class several years ago but I had forgotten that use of the $
symbol makes the cell references absolute. Thanks for the reminder as
I've never put this symbol in practice. 

And, I did find some info in the Help contents, but your examples
referencing my formula directly helped. 

Appreciate it, Pete! :)


-- 
Emil0
------------------------------------------------------------------------
Emil0's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31232
View this thread: http://www.excelforum.com/showthread.php?threadid=508999

0
2/6/2006 8:48:46 PM
Well, thanks for getting back: I'm glad you got something out of it.

Pete

0
pashurst (2576)
2/6/2006 9:10:32 PM
Reply:

Similar Artilces:

How to omit certain cells in formula
Hello If I have monthly data in a table, but data form April on is not available yet, but I would like to be inputted automatically from some other table when it becomes available there. So I put in the cell a formula =DATA!I49. And as the source is empty so far- I get the result of the formula '0'. But at the end of this line (that now includes data till March and for the next 9 months there are 0) I have formula for average- which by now also takes in to account the 0 in the line- which I do not want. What would be the best way to tell the excel that: calculate average in the lin...

Function or Formula to Add lowest X number of values in a range
I'm looking for a way to total (SUM) the lowest X number of values in a given range of values. For example, I have a row of 15 values. I'd like to do some analysis and determine what the total and average is for the lowest 5 values, lowest 10 values, or whatever number of values I desire. Is there a function or formula that will help me accomplish this? Ctrl/Shift/Enter: =SUM(SMALL(A1:A15,ROW(1:5))) for the smallest 5. If you want the smallest X, where X is in cell D7, for example, ctrl/shift/enter: =SUM(SMALL(A1:A15,ROW(INDIRECT("1:"&D7)))) Bob Umlas Excel MVP ...

Help with scatterplot
I need some help with my assignment: They�re asking me to create a scatterplot of data to investigate the relationship between machines and maintenance, puting # Machines on the x-axis and # Minutes on the y-axis. Can you explain me what a Scatterplot is?, I�m asking because I think it�s not a normal graph. ANy advice is appreciate. Thanks. Jackov- Did you really need to ask that to all those groups, when you can use Yahoo or Google to find out what a scatter plot is? A scatter plot will show you your data points in a graphical way. "Jakkov" <google@pichara.com> wrote ...

How I do this formula ?
I would like to format all words that said �customer� in a column to chosen color.Can anyone provide a formula or conditional formattin that will do this -- HR15 ----------------------------------------------------------------------- HR157's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3445 View this thread: http://www.excelforum.com/showthread.php?threadid=54371 You could apply an autofilter to the column and select Custom | Contains then enter customer. This will only display those rows which contain customer in that column, and you can highlight the cells...

Help with employee OEE
Need some help with OEE for the current company i am working for. Basically i need to set up a spead sheet that will allow me to enter a quantity and hours worked and provide me with there overall efficency for the week and then month. These formulas also would also need to be able to work over 3 workborks and allow the efficencies the compute for the machies as well not only the employees. What the spread sheet looks like is as follows:\ B3-H3 is an employee name, B4-H4 would be an empty box for the prodution number. The obvious answer would to just enter the data and use an average func...

Combo Box Help
Hi All, I have a combo box on my form. It is unbound to form. I am obtaining cboBox data from another table in database. However I need to the cboBox to only list records that match the customerID from the form it on and the customerID in the cboBox data. How can I do that? -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200702/1 On Fri, 23 Feb 2007 06:43:59 GMT, "mattc66 via AccessMonster.com" <u16013@uwe> wrote: >Hi All, > >I have a combo box on my form. It is ...

Help with restoring Microsoft Money 2005
I am hoping that there someone that can hear this plea. A client got hit with the rogue virus. They had backed their files on the PC. I was able to copy the files off before reinstalling win xp. They reinstalled money 2005 but it will not let them restore their backup. Gives them an invalid drive spec. In searching all the help sites, I understand there was a quick engineering patch that helped this. Please any help would be greatly appreciated. Everthing I have read goes back to 2006 when there was support for 2005. If you want you can email be privately at drdigital99...

how to copy&paste range with formulas which are without frozen cells ($) ?
hi, I've created a monster - huge reporting file, it takes lot of inpu data and play with it in many ways in many workseets to create m monthly 16page Production control and logistics report(6 divisions european curriences, fx rates, delivery performance, and lot more) a its very big I can coordinate cell links anymore. My problem is: i have range with formulas, and result of those formula im useing in God knows how many other formulas etc. I want to chang those cells slightly so everything will suck new results, but i want t keep also old calculation somewhere(next to it) if: -I copy a...

formula #30
hi all i want to write formula for one of my excel column, but it`s complicate. my formula has 3 if in if ! what should i do? -- y.ranjbaran On Mar 7, 8:48=A0am, y.ranjbaran <y.ranjbaran.99d1...@excelbanter.com> wrote: > hi all > i want to write formula for one of my excel column, but it`s complicate. > my formula has 3 if in if ! what should i do? > > -- > y.ranjbaran You need to provide a few more details please if we are to help you. 'trip_to_tokyo[_3_ Wrote: > ;1558546']On Mar 7, 8:48*am, y.ranjbaran > <y.ranjbaran.99d1...@excelbanter.com...

Excel Formula needed
12864 decreased by 3% for 19 years. I would like the decreasing value in the fist column and the difference in value in the second column. as an example: 12868.00 0000.00 12478.08 385.94 Thanks in advance In article <W74rn.10625$Ek4.7339@newsfe24.iad>, joemocasanto@aol.com says... > > 12864 decreased by 3% for 19 years. > > I would like the decreasing value in the fist column and the difference in > value in the second column. > > as an example: > > 12868.00 0000.00 > 12478.08 ...

year formula 04-13-10
I'm sure this is very simple, but for some reason it's not working for me. I have a log of training sessions provided to clients. I want to run a pivot table that totals the number of hours for the year. I track the date in the spreadsheet, so I wanted to add a column of "year" so that I can easily get totals per year. I want to reference the date column to populate the year for that date (I have hundreds of lines already so I don't want to do it manually) When i added the column & used the year function, it comes up with 1905 each time instead of...

Multiple signatures, Multiple accounts...please help!
I'm using Outlook 2003, and have 3 different accounts coming into one Inbox. What I am looking to do is have my auto-signature change to the correct one depending on the email account I choose to send the message from. Is this possible? I am unable to set up different profiles, and really don't want to do that, so I am stuck with 3 accounts in one Inbox. Work had us on Macs, and Outlook had this ability there, so I'm assuming that it can be done here, since MS is primarily a PC based company. Please help! I just need to know if it's possible or not. ...

Excel 2003 formula
I'm trying to make a formula for a list to check and strike out another on 3 sheets. Only 2 are using the info, sheet 3 has various colums to check. Sheet 2 A B dog yes cat (blank) Sheet 3 A C E etc... Dog(striked) Blank Dog(striked) If sheet 2, B is yes then check Sheet 3 colums and strike them out or change color. Hope that made sense. Similar to a called off list. Please help me if you can. TIA Ginger If you are familiar with conditional formatting you could apply conditional forma...

Sharing Help
I'm looking to be able to keep an excel workbook on some webspace and allow others in a group to access and edit it directly. What is the best (most efficient way) to do this? "ZachKS" wrote: > I'm looking to be able to keep an excel workbook on some webspace and allow > others in a group to access and edit it directly. What is the best (most > efficient way) to do this? > > > In excel Under Tools -> Share workbook checkbook "allow more than one people make changes" And in the Advanced tab you can change rules of share. after ...

#VALUE error add'g formulas
Trying to add two rows of times with the result in one cell, but having trouble. When I try to add the two rows together, I get a “#VALUE” result. The result for each row displays fine in the HOURS WORKED column on an individual/per-row basis –– but I need to display just one result for both rows - in the “Row 2” cell of the “HOURS WORKED” column for that day. The formulas for each row are shown below. Times are written in standard AM and PM format. The IF statement are for controls to display standard (12-hr.) time format, a text option for writing in notes, and so that &...

Help with Code
Hi All, I am having the following code which stpos after copying data from the first instance of the sheet. How cud i fix this problem? Sub CurMonth() Sheets("CasCrd").Select Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Delete Shift:=xlUp Range("A1").Select Sheets("Interface").Select For Each Sh In Sheets Sh.Select If Mid(Sh.Name, 2, 2) = Right(Sheets("Interface").[C2], 2) Then Range("A1").Select Selection.AutoFilter Field:=4, Criteria1:="N" Selection....

Help: Not everyone in distribution group receiving emails
Hello all. I have a question that I'm hoping someone can help shed some light on. Running Exchange 2003 SP1 on a Win 2003 server... We have a particular distribution group setup with seven people in it. Yesterday, some emails were sent to the group from a client of ours. Five of the seven received the email with no issue. However, two of them did not receive the emails. This has never happened before. I checked the application logs and there are no errors or warnings suggesting why those users couldn't get this particular email. I sent an email a few minutes ago to the group as ...

Address book causes Outlook to close: Please Help!
I'm using Outlook 2000 Pro with Windows XT. When you open the address book, then select u,v,w,x,y,or z, Outlook immediately closes. PLEASE HELP! Thank you. .. >I'm using Outlook 2000 Pro with Windows XT. When you open >the address book, then select u,v,w,x,y,or z, Outlook >immediately closes. PLEASE HELP! Thank you. "Windows XT"? You probably mean Windows XP. Possibly a corrupt Contacts folder. Try either the Inbox Repair Tool (scanpst.exe) or create a new PST, set it as your default delivery location, and copy everything you can from the old PST befor...

HELP!!!! PIVOT TABLES
I am 17, and have just accepted a new job to start in 2 weeks, only problem is, it involvs using pivot tables. Please don't think I'm stupid, but have never used these before and am not even sure what they are, let alone how to do them!!!!!!! Any help would be greatly apprieciated!! To summarize the data in a table, you can use a Pivot Table. There are instructions in Excel's Help, and Jon Peltier has information and links: http://peltiertech.com/Excel/Pivots/pivotstart.htm Dawn (17) wrote: > I am 17, and have just accepted a new job to start in 2 weeks, only prob...

Help with calculations
I have my company products pricing on an excel spreadsheet. It contains several items and has 3 brackets. At times I need to deviate from it , either by a percentage, say 10% or by an exact amount, say .75/case. Is there a formula I can plug into this spreadsheet that will calculate the deviations without me having to manually make the deductions for each individual item. Anyone's help would ge greatly appreciated. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/...

Using MS Outlook as a client to Lotus Notes-Please help
We use Lotus Notes as the mail Server. I would like to use MS Outlook or Outlook Express as the mail client to Lotus notes. Please help how do I setup Outlook as a client to Lotus Notes a step by step procedure is appreciated. Thanks Karen refer here: " Connecting Microsoft Outlook to Lotus Notes" http://www.slipstick.com/addins/SERVICES/lotusnotes.htm "Karen Middleton" <karenmiddleol@yahoo.com> wrote in message news:a5fd468a.0503181805.24086cce@posting.google.com... > We use Lotus Notes as the mail Server. I would like to use MS Outlook > or Outlook Express...

Outsource your web development, CMS, CRM, ERP, Ecommerce, Elearning, software projects, we are here for help
We are dedicated php, ajax and open source programmers working in delhi, India. Looking for projects all over the world we can work on any projects in any regional language. We have-: * Strong familiarity with object oriented programming, ZEND, MVC framework. * Social networking platform websites, integration. * Development, customization of Open Source technologies like Joomla ( Component level development and customization), Wordpress, Drupal and open source e commerce platform like X-cart, Zen Cart. * Experience integrating third party API=92 s, Google Maps , Facebook and ...

Help with Repair of CRM #.0
I am repairing CRM 3.0. the repair validation completes fine and the repair begins. When it gets to the "Installing SQL Server Reporting Services" , "Validating Samples Database", I get an error message: "Cannot connect to the SQL Server Instance" and the repair fails. Any ideas? can you skip past this? Reporting in crm 3.0 is like reporting in crm 1.2 in that crm can work without it meaning you can fix it later -- ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "brellim" <brellim@discussio...

Getting Relayed and can't stop it. Please help.
Hello, Running 5.5, sp4 on NT 4.0 sp6a. I have 300 users with 50 of them being POP (we are converting them to full server side mail) using Outlook 2K and 2002. Getting lots of complaints from users who state they're getting NDR's with their address from users they know nothing about. I have about 100 messages in the IMC queue each morning with the typical <> that I just delete. Routing tab is set to route to internal domain (works fine). Under routing restrictions, nothing is checked at this time. I was told if I check 'Hosts and clients that successfully authenicate...

Help
Hi, We have a two till setup with the master database on one of the tills. For some reason on we seem to lose connection to the database during the tendering of sales and the POS crashes. This results in losing the sales information too so hence the stock numbers become incorrect. It's a nightmare! The network between the till's has been checked, however this problem even happens on the Till where the database resides so this is not the problem. I've also noticed that the tills run slow sometimes. There are no other processes running on the system other than the POS...