#### Which formula do I need?

```Hi!

I have a sales spreadsheet showing sales per month for last year versus
the current year.  The data is input for all of last year and the data
for the current year would be available through the current month.

There are totals at the end of each row and I'd like the total for the
prior year to add through the current month.

Example:

First Row:      	JAN thru DEC sales
Second Row:    	JAN thru AUG sales

The total column for the first row would only add the sales from JAN -
AUG.

I tried using SUMIF but don't think I'm doing it right and I don't think
having 12 nested IFs is the way to go...

TIA!!!

dave
```
 0
12/8/2004 4:13:07 PM
excel 39879 articles. 2 followers.

2 Replies
472 Views

Similar Articles

[PageSpeed] 51

```Hi Davey Boy

assuming your last year values are in row 2 and the current year values are
in row 3 this formula should work for you
=SUMIF(B3:M3,"<>"&"",B2:M2)

Cheers
JulieD

"Davey Boy" <mikioiTAKEOUT@gte.net> wrote in message
news:Xns95B93F473E4B9mikioigtenet@199.45.49.11...
> Hi!
>
>
> I have a sales spreadsheet showing sales per month for last year versus
> the current year.  The data is input for all of last year and the data
> for the current year would be available through the current month.
>
> There are totals at the end of each row and I'd like the total for the
> prior year to add through the current month.
>
> Example:
>
> First Row:      JAN thru DEC sales
> Second Row:    JAN thru AUG sales
>
> The total column for the first row would only add the sales from JAN -
> AUG.
>
> I tried using SUMIF but don't think I'm doing it right and I don't think
> having 12 nested IFs is the way to go...
>
> TIA!!!
>
> dave

```
 0
JulieD1 (2295)
12/8/2004 4:20:41 PM
```Hi
one way:
=SUMPRODUCT(--(A2:X2<>""),A1:X1)

--
Regards
Frank Kabel
Frankfurt, Germany

"Davey Boy" <mikioiTAKEOUT@gte.net> schrieb im Newsbeitrag
news:Xns95B93F473E4B9mikioigtenet@199.45.49.11...
> Hi!
>
>
> I have a sales spreadsheet showing sales per month for last year
versus
> the current year.  The data is input for all of last year and the
data
> for the current year would be available through the current month.
>
> There are totals at the end of each row and I'd like the total for
the
> prior year to add through the current month.
>
> Example:
>
> First Row:      JAN thru DEC sales
> Second Row:    JAN thru AUG sales
>
> The total column for the first row would only add the sales from
JAN -
> AUG.
>
> I tried using SUMIF but don't think I'm doing it right and I don't
think
> having 12 nested IFs is the way to go...
>
> TIA!!!
>
> dave

```
 0
frank.kabel (11126)
12/8/2004 5:03:27 PM

Similar Artilces:

Bills and Deposits Help Needed
I have been trying to delete old bills and deposits in Money 2006. I deleted by budget, but on the homepage it still lists my overdue bills and when I click on the Bills Summary page it is empty. However, the calendar still show what is due. Please help...this is making everything innacurate! Thanks, Jana We'd love to help, but we're not sure how. Are you using "Advanced Bills" or "Essential Bills"? I don't necessarily know a solution in either case, but someone might. "Jana" wrote: > I have been trying to delete old bills and deposits in ...

Need help pulling data from cells in different workbooks
Hi, let me try and explain this the best I can: say i have a workbook with 2 pages, on the first sheet, in cells C27 C47, C67 and C87 on the Sheet2, I want to make a list. in that list contains data fro cells C27, C47, C67 and C87 from Sheet1 . on sheet2, the list wil look like: List ='Sheet1!C27 ='Sheet1!C47 ='Sheet1!C67 ='Sheet1!C87 Is there any easy way to pull that data without doing each lin individually. I know i can put in the = and pick the cell on the othe sheet, but I have 100's of cells I need to get data from. If i try an copy paste each line, it does...

Almost got it, but now I really need help
Hello i have a project where i am trying to open a folder to which my app will only know the first five characters of. here is my code for the function: Public Function fsFoldersearch(strProdType As String, strFolderSerial As String) Dim strFolder As String Let strFolder = "C:\WO\" & strProdType & "\1 Completed\" & strFolderSerial & "*" Shell """C:\WINDOWS\EXPLORER.EXE""" & " " & Chr(34) & strFolder & Chr(34), vbNormalFocus End Function i thought maybe a wildcar...

Table reference syntax needed
I know that this is not actually a query. I have a form that has a value set in Me.ServiceID. In a table Services (which is NOT the data source for the form) there is a pair of values, ServiceID and ServiceName. How can I find (specify) the ServiceName using Me.ServiceID? I have an IF statement assigning Me.Description equal to this value TIA Ron "Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message news:BF961C24-4A33-48D9-90D6-BAC50B50C863@microsoft.com... >I would include the table Services in your form's record source. If you >can't > do ...

Formatting a cell for color that has a formula also
I am using this formula =If (\$A\$1="","",A1+1) so that when i put a date in it adds the next date to it. I want to be able to get Saturday and Sunday to change to a yellow color to note that its a weekend date can this be done? I can do it manually but would like to be able to do it automatically if possible. Thanks Karl Select your range (all at once) With A1 the activecell, do: format|Conditional formatting formula is: =weekday(a1,2)>5 give it a nice format. I like to give date cells a custom format of: dddd* mm/dd/yyyy Karl wrote: > > I am using this...

IF Formulas
If any one can help with this query would be much apprieciated i'm very in need of it thanks; Simple example of what i need; on my work sheet i have a row with monthly interest rates in as column headers i.e 0.3 0.5 0.7 0.8 as under each header is a figure i.e under 0.3 is 5 under 0.5 is 10 ect. I have a formula which gives me a figure under the colounm header so say my formula through out 5 i need to display in a cell the column header so 0.3. So i need something that says IF cell=5 display 0.3 OR IF cell=10 display 0.5 OR IF cell=x display y. Your help really appriecited thank y...

hourglass won't go away after formula was entered
This question is for my husband. He's at home working on a homework assingment in excel. He said he entered a formula then the hour glass came up and it hasn't gone away. He can't close without saving. He's going to have to shut the system down without saving 3 hours of work. Any suggestions? Thanks for you time. Try the ESC key or hold CTRL and press Break key best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Dawn" <Dawn@discussions.microsoft.com> wrote in message news:C61F39DE-90E6-402B-8EB9-4C1A7F667B61@microso...

POS 2.0 Database
I just installed the MS POS 2.0 on a brand new HP (w XP Pro) with a sample database. Now I am ready to delete everything and start my own store's DB. However, even though I deleted all 3 copies of the sample databases I previously created (via Tools>Dayabase>Delete) I still see the sample database items and configuration specifics. How can I create a clean DB for my store? What is the meaning of Db in MS POS? Is there a non-listed DB somewhere that does not get to be listed in the DB list (for deleting)? THANK YOU VERY MUCH IN ADVANCE. -Delen HI, how you make deletion, by us...

Monthly charts do not retail formula row/column
1) Each month I do a "save as" to copy worksheets to the next month. Even though the cell references in the formulas are absolute, the range changes for the new month. For instance - I have data in Shift1 in February that goes from A1 to Z100. When I "save as", and clear the Feb Shift1 data to enter the March data, the formula =SUMIF('SHIFT 1'!\$L\$1:\$L\$517,"HB",'SHIFT 1'!\$T\$1:\$T\$1) may read =SUMIF('SHIFT 1'!\$L\$1:\$L\$23,"HB",'SHIFT 1'!\$T\$1:\$T\$1). [\$L\$517 changes to \$L\$23]. I thought the \$ in front of the row/colum...

Formula or Macro needed?
Hi, I need some help desperately, for reasons which are too complicated to go into right here and now, I need a formula or a macro to produce an excel spreadsheet of 16 columns and 16 rows where each row and each column contains the numbers 1 through to 16 without the same number repeated on any row or any column - I know it may sound a little tricky :eek: but I'm hoping somebody is up for a challenge like this and can help rather quickly - I'm not interested in the number of permutations or anything daft like that - I just need a solution. Thanks to anybody who knows and can post...

I don't know if this behavior can be adjusted. Say I have a formula in cell C2 like this: =SUM(A2,B2). when i drag the fill handle to the cell to the right and choose copy cells from the auto fill options, the formula changes to: =SUM(B2,C2) returning a different result. It still changes also when the fill handle is dragged downwards. How can I drag the fill handle and copy cells with auto fill and still get the same formula in the adjacent cell or new cell. TIA -- two things i learnt from life: the hardest things are the simplest of all; they just take time the simple things ar...

Lock or freeze A portion of a formula
I want to lock or freeze a portion of a formula when I drag a formula o copy it Ex. =FREQUENCY(A1:A217,J8) I want to freeze the portio A1:A217 but I want J8 to chang relative to the location Thanks in advance G -- Message posted from http://www.ExcelForum.com Hi GT! Use: =FREQUENCY(\$A\$1:\$A\$217,J8) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "opus234 >" <<opus234.17vhyt@excelforum-nospam.com> wrote in message news:opus234.17vhyt@excelforum-nospam.com... >I want to lock or freeze a portion of a formula when I drag a formula ...

linking to formula template on different tab
I don't know if I can do this in Excel, but thought it worth asking. I have a list of 80 clients. I need to know the cost for them, which is a complicated, multi-step calculation. I enter 4 input #s into the calculation (50, 17, 0, 23), and out pops the results: \$22,123, \$33,123, 0, \$99,123. (I created the multi-step calculation formula, btw) However, the #s for the clients change regularly. So now I need to enter my new set of #s (45, 17, 2, 28) in order to get the updated cost figures. And I need to do this for all 80 clients, every time. In addition, I often need ...

Sorting using formula
I have the following rows A B C D E Bill A 5 1 Bill B 8 Bill B 8 2 Bill A 5 Bill C 2 3 Bill D 3 Bill D 3 4 Bill C 2 I column D I have this formula =INDEX(\$A\$1:\$A\$4,ROW(INDIRECT(ADDRESS(MATCH(LARGE(\$B\$1:\$B\$4,C1),\$B\$1:\$B\$4,0),1))),1) That I use to produce the list and in column E =LARGE(\$B\$1:\$B\$4;C1) Actually to sort the two first rows without sorting them. But the problem is, that if two or more entries in column B is the same, it does not work. If Bill B and C both has the valueof 8 the list in column D will appear as...

Principal & Interest Formula
Hi all, I am trying to complete a principal & interest formula that remain empty in the target cell if no loan amount is entered, =IF(E20="","")*PMT(H20,30,-E20) E20 = Loan amount H20 = Interest Rate L45 = Target cell With the above cell i get Value as an answer. Can anyone help me out with completing this formula? -- Thanks in advance. Scoober =IF(E20="","",PMT(H20,30,-E20)) -- Kind regards, Niek Otten Microsoft MVP - Excel "Scoober" <Scoober@discussions.microsoft.com> wrote in message news:B...

Help needed. Word question about segmented line..
I'm typing a manuscript. Somehow I must have pressed something wrong because I've gotten a segmented line after a number of paragraghs that I can't delete. They just shift up and down. Does anyone know how this happened and how to get rid of these lines? Thanks. Look up "borders" in the Word help. What you have is a paragraph border. The Help explains how to remove it. "How it happened" is probably due to Tools>AutoCorrect>Autoformat as you type." Look that up in the help too. The AutoFormat dialog is in three sections: I recommend turning OFF...

Hi im new to Excel and need info on Macros!
Hi, Ive been trying to understand macros for about a week now jus using the help columns in Excel. I cant figure out anything. I remember doing sothing in school to do with macros and creatin buttons which moved you fro sheet to sheet from just one page. I nee to remember how to do this. the thing im stuck on at the moment is somthing to do with macr codes??? PLEASE HELP ME -- SarahKa ----------------------------------------------------------------------- SarahKay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2433 View this thread: http://www.excelforum.com/s...

Fill Options & Formula Help
I am trying to drag a formula across many cells from a seperate spreadsheet, however i am having a few difficulties. On the second sheet i am wanting to fill cells horizontally (a1, a2, a3 etc) with information from the first sheet (a1, b1, c1, d1 etc). For some reason i cannot do this without manually going through and changing the formula. Is there a way this could be acheived? Thank you GOto sheet2 cell a1 Type the "=" to start the formula Switch back to sheet1, select cell A1, press enter Now use your normal fill handle to replicate this formula across shee...

Crystal Report Formula for FIFO Costing Calculation
I am writing a report in V9.0 Crystal Reports and having an issue with a formula, hopefully someone can help. My situation is this: If I have a Quantity of 10 on an Order and my Receiving layers are as follows • 1/1/06 – Qty 2 – 10.00 • 1/5/06 – Qty 5 – 14.00 • 1/8/06 – Qty 20 – 16.00 What should happen is if this Invoice is the First Invoice Number or the earliest Invoice Number with this Item then upon Posting the Invoice it will Grab the 2 for 10.00, the 5 for 14.00 and 3 of the 20 for 16.00, giving me an Average cost of 13.80 per unit. I need the report to formulate the same concl...

Average every seven cells, then copy formula
On my Averages worksheet, I have the following formulas: In Cell A2: ="Week " & WEEKNUM(Sheet1!E2,1)& " - " & TEXT(Sheet1! E2,"mm/dd/yy") In Cell B2: =AVERAGE(Sheet1!F2:F8) 'compute values based on blocks of 7 successive rows I'd like to be able to COPY these formulas to row 3 so that the formulas in Row 3 begin on Row 9 of Sheet1, Like this: Cell A3: ="Week " & WEEKNUM(Sheet1!E9,1)& " - " & TEXT(Sheet1!E9,"mm/ dd/yy") Cell B3: =AVERAGE(Sheet1!F9:F15) 'compute values based on blocks of...

Formula for summary from two column
Date Fruits Quantity Fruits 2007 2008 2007 Apple 10 Apple 2007 Orange 56 mango 2007 Mango 48 watermelon 2007 Pineapple 87 orange 2007 Mango 125 strawbery 2007 Citrus 45 citrus 2007 Banana 987 banana 2007 Pineapple 265 grape 2008 Citrus 485 papaya 2008 Banana 698 pineapple 2008 Grape 463 2008 Papaya 216 2008 strawbery 254 2008 Banana 744 2007 Orange 885 2007 Apple 1236 2007 Citrus 1214 2008 watermelon 1546 2008 Apple 8952 2008 Papaya 874 2008 Banana 236 2008 Pineapple 95 2008 Orange 4123 2008 Citrus 236 2007 Banana 216 2008 Mango 956 2008 watermelon 211 2...

hide formulas but can edit on the same cells
Hello, How to I hide and lock the formulas that appear on A1 to A10 let's say, without locking them? I need to be able to copy and paste the results so I need to be able to click on them. I also need to share the document, but do not want people to see the password nor manipulate them. Thanks. You can hide the column, but if the sheet is not protected, people could simply unhide the column to see its contents. If the information is on a protected worksheet, people will be able to see it, either by copying and pasting the data to another sheet, or by using a password cracker to...

need help with Outlook/Excel interface
how can I copy multiple Excel files from Outlook (all at once) into Excel? Is there a way to do this? When I right click ONE file attached to the email, I can copy it, either using My Computer, or opening the directory within Excel. BUT, if I clik 2 or more, and try to copy & paste, it doesn't work. I have 16 files to copy. Can you help? I've been looking online using microsoft "Help" for over 30 minutes (not too helpful!). Simple searches do NOT yield a solution!! Any real help you can give me is appreciated. Thanks. Mark I have only been able to save them a...

Need formula to Check Duplicates
Hi everyone, I need a formula that will put the word duplicate in a column based on some codes in another column, leaving the first code blank, then all subsequent times it comes across the same code, it will put the word "duplicate". I generally use the following formula, but this puts the word duplicate next to the first ones it comes across then leaves the last one blank. I need to keep the data in the current order so I can't sort the data. =IF(COUNTIF(a2:\$a\$130,a2)>1,"Duplicate","") Thanks Dan =IF(COUNTIF(\$a\$2:\$a\$130,a2)>1,"Duplicat...

using if function to specify range in formula
I am trying to use the median function, but only apply it to certai values in a range. It is easy to do this with the average functio using sumif and dividing it by countif, but is there any way to do thi on other functions -- Message posted from http://www.ExcelForum.com Hi use the following array formula (entered with CTRL+SHIFT+ENTER): =MEDIA(IF(A1:A100=your_value,A1:A100)) -- Regards Frank Kabel Frankfurt, Germany > I am trying to use the median function, but only apply it to certain > values in a range. It is easy to do this with the average function > using sumif and divi...