Sum of values in row if they also correspond to a month?

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Column headings to numbers and row headings to alphabets? Hi there, My spreadsheet has gone crazy and now the column headings are in numbers instead of alphabets and at the same time, the row headings are in alphabets instead of numbers. Can anyone advice on how to rectify this? J Tag: Sum of values in row if they also correspond to a month? Tag: 783633
  - 2
    - Filter - Copy info Hi I have a file that I filter. Having found the required records, is there a way to copy one of the columns data into a new column but ensuring the data gets copied into the same row as the filtered records. Normally I highlight the column of data for the filtered records Use GoTo visible cells only Copy Select first cell in next column Paste The data is pasted from that cell down but doesn't get copied into the filtered record rows. Hope this makes sense. Help!! Tag: Sum of values in row if they also correspond to a month? Tag: 783631
  - 3
    - A highly profitable system? RSpence Life is for living Knowledge is the key to success! http://www.forex-killer.com/?hop=rspence1 Tag: Sum of values in row if they also correspond to a month? Tag: 783629
  - 4
    - vlookup I have revenue numbers for 2007 and 2008 in columns, I need to do a vlookup (i think) that will classify into four columns demarking wih a check mark whether column 1 - the revenue increased, column 2 - revenue was new for 2008, column 3 - decreased and column 4 -if it decreased whetheth the business went away or sales were lower in 2008. I'm fustrated because I know what I want to do and know it's possible, but can't figure out what the formula is - help! -- Thanks, Jennifer -- Thanks, Jennifer Tag: Sum of values in row if they also correspond to a month? Tag: 783626
  - 5
    - Drop down list with external hyperlinks I would like to set up a data validation where the items in the drop-down list are external hyperlinks. If I put complete URLs in my list, they don't show up as active hyperlinks unless the user happens to double-click or F2 the cell to activate autocorrect and cause the link to become active. Is there any way to get an active hyperlink from a drop-down list? Furthermore, what I really like to be able to do is have the items in the list be true hyperlinks where the text is a hyperlinked label, not a full URL. That is probably not possible, but any help would be appreciated. TIA Tag: Sum of values in row if they also correspond to a month? Tag: 783622
  - 6
    - Minimising the Ribbon in an Auto_Open macro Hi, I am trying to get an Auto_Open macro to minimise the ribbon in Excel 2007, but the Ctrl+F1 won't appear in the macro. Can someone please let me know what I am doing wrong ? -- Thanks in advance, Jeff Tag: Sum of values in row if they also correspond to a month? Tag: 783620
  - 7
    - Given a VALUE in a table, ascertain RANGE in another table I have month values in cells A1:A12. I have a relative range name called Date whose refers to value is '=$A1'. So in row 7, Date refers to A7, and in row 11, Date refers to A11, etc. The values in A1:A12 are derived from a larger SourceTable on another sheet. Given Date (column A of the current row), I would also like to be able to define the cell in SourceTable, so that I can ascertain a range based on that cell. I hope that's clear, but I can explain further if required. Thanks in advance Paul Martin Melbourne, Australia Tag: Sum of values in row if they also correspond to a month? Tag: 783617
  - 8
    - Change in Column Width depending on whether printer is installed / Hi, I need to set a specific layout for a sheet having A4 size. By default, the rowheight=18pixels & column width=66 pixels in case the machine has printer installed. If the printer is not installed, then on opening the same excel, the row height becomes 71 pixels & row width becomes 18 pixels. WHy does this happen? Is there any way to have the same size irrespective of printer setup? Tag: Sum of values in row if they also correspond to a month? Tag: 783616
  - 9
    - Countdown Hi, I was wondering if anyone would be able to tell me the formula to countdown the days, hours, minutes and seconds from the time I open a spreadsheeet until a given time in the future, Cheers Tag: Sum of values in row if they also correspond to a month? Tag: 783615
  - 10
    - Creating a profit and loss from MSQuery Data Can someone point me in the right direction for the best way to program or formulate a profit and loss (and other financial reports) from raw data tables. For example the raw data is available via ODBC in the format of a general ledger GLACCT DESC BEGINBAL PD1BAL PD2BAL PD3BAL PD4BAL ...... 1000-00 CASH 10002.00 -20.93 23.00 16.04 100.00 2000-00 A/R 2340.00 102.03 27.42 13.49 40.59 4000-01 SALES 1992.93 999.83 288.42 100.00 4000-02 SALES 1992.93 999.83 288.42 100.00 5000-01 COGS 934.23 253.65 35.67 34.64 5000-02 COGS 934.23 253.65 35.67 34.64 6100-01 UTILITIES 100.00 100.00 100.00 100.00 6100-02 UTILITIES 100.00 100.00 100.00 100.00 ETC ETC What is the best way to build a report from this data that is flexible enough to provide a profit and loss that can be updated by simply refreshing the data query? I dont want to have to rebuild the report every time a new expense acct is added. If I am looking for something like this how should I build the spreadsheet... I am quite capable with access but I don't know how to structure this in excel would you make a sheet for the query data, then pull numbers to named ranges with vlookup from another sheet? Any thoughts or Ideas would be greatly appreciated. current ytd later I will be adding historicals Sales 1992.93 12312.10 for ytd last year 2 years ago etc COGS 934.23 6102.21 Gross Profit 1058.70 6209.89 Utilities 100.00 1200.00 ... ... Total Expenses 212.00 2400.00 Net Profit 846.70 3809.89 Tag: Sum of values in row if they also correspond to a month? Tag: 783612
  - 11
    - Arrow key I did something to my excel worksheet that caused my arrow key not to work anymore. I cannot move the cursor between cells. It just moves the whole page up or down. How do I get back to moving the cursor between cells? Dave D. Tag: Sum of values in row if they also correspond to a month? Tag: 783611
  - 12
    - 'spin buttons' & vbasic language i have posted this question B4, Norman Jones did reply, but i'm confused (not hard) Iâ??m having trouble with Normans explanation. Would it be too much to ask for a greater explanation? Iâ??m not sure where to substitute my data for that required by Vbasic. To refresh, My spin button is in sheet2 By clicking on the spin button (up) I want to copy a selected group of cells A1:Q12 from sheet1 and paste into sheet2 A5:Q17, when I spin (down) I want another group of cells to be copied. Ultimately Iâ??m looking to browse thru sheet1 by clicking the spin button up/dwn and read the selected group of cells (from sheet1) in sheet2. Iâ??m not even sure Iâ??m using the correct command (spin button) maybe thereâ??s a better way, Can you advise? -- Browny Tag: Sum of values in row if they also correspond to a month? Tag: 783607
  - 13
    - How to attach a file to an excel sheet? Hi there, Sorry if this question has been asked before. I got an excel file, in one of it's sheets there is a "link"/button, when I double click to that button, it open up a new excel file with content inside. I would like to know how to attach the file to a sheet like that. Thanks. -- Target_locked Tag: Sum of values in row if they also correspond to a month? Tag: 783604
  - 14
    - Problems with trendline equations I have several worksheets of data. I am plotting/trending the data on separate worksheets, and calculating the equations of exponential trendlines. I have as many as six trendlines per graph. As I add more worksheets of data and make more graphs, the graphs that I previously made become cluttered with several trendline equations. When I delete the extra trendline equations from a given graph, Excel shuts down. I am using Microsoft Office XP Standard for students and teachers version 2002. Any advice would be greatly appreciated. Thank you. I mistakenly posted this question on the Office discussion groups earlier, I'm not intentionally multi-posting! Steve Tag: Sum of values in row if they also correspond to a month? Tag: 783599
  - 15
    - Sum Formula for cell range How do I multiply a number in a range of cells by a number? EX Cell 200 Multiply by 2.25 Tag: Sum of values in row if they also correspond to a month? Tag: 783597
  - 16
    - where do I find capital leasing calculators? where do I find capital leasing calculators? Tag: Sum of values in row if they also correspond to a month? Tag: 783596
  - 17
    - Can I use conditional formatting to scale a display value? I have a value in a cell, I would like to display the value directly with a text modifier if it is above a certain threshold or display a scaled value with a different text modifier if it is below a threshold. I use the value in a different calculation so I don't want to change the value of the cell, only the way it displays. Examples: If the value is time: .5 should read as "4 hours" while 2 should read as "2 days" If it was money: .5 should read as "50 cents" while 2 should read as "2 dollars" Thanks. Tag: Sum of values in row if they also correspond to a month? Tag: 783595
  - 18
    - Quick Sumproduct vs Text return Hi Team, I think I've lost it :P I've got two lists. c d e 9 Morry 2000 2004 10 Howie 2005 2009 11 Johno 2010 2019 12 Smithy 2020 2029 13 Jones 2030 3000 and c d 19 John 2005 20 Sam 2010 21 Ralph 2011 22 fred 2001 23 mike 2020 24 Nat 2008 25 Sally 2015 26 steff 2025 27 diane 2027 I want to pull the name from list 1 and place it in column e if the number in list 2 column d falls in the range in list1 columns d&e I have this formula working to an extent =SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19)*($C$9:$C$13)) This returns a #value error. if I do some checking and modify list 1 to add a unique number in column f =SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19),$F$9:$F$13) It returns the correct unique number..... So I figure the problem is returning the text?? Changing the forumla to this =SUMPRODUCT(--($E$9:$E$13>=$D19),--($D$9:$D$13<=$D19),$C$9:$C$13) does not work as it returns all 0. any help is much welcomed. Tag: Sum of values in row if they also correspond to a month? Tag: 783594
  - 19
    - How to send Excel selection using Live Mail I have been using Outlook Express until this month, and have been sending information from a selected range in an Excel worksheet to a number of people every month. I don't want to send the entire (large) worksheet as an attachment, but my "Mail Recipient" function has apparently been disabled since installing Windows Live Mail. The only "mail" option I see remaining is to mail the entire document as an attachment, which really isn't an option. Since I send this information out to about 50 recipients each month, and each recipient has different data, it's been tedious enough already. But now, I'm looking for a way to send the data using Windows Live Mail without having to cut and paste all afternoon. If there is a way to efficiently do this using a Macro, that would be even better... Thanks, Tag: Sum of values in row if they also correspond to a month? Tag: 783593
  - 20
    - Insert word to excel If I embed a word document, it becomes a picture of the first page of the word doc when I'm not in edit mode. I need to view the full word doc alongside the spreadsheet data. Any suggestions? Thanks Tag: Sum of values in row if they also correspond to a month? Tag: 783590
  - 21
    - Adding up dropdown menu categories. I created a dropdown list in my worksheet and I was wondering if there was any way to add up how many times each individual item was selected. Any help would be appreciated. Thanks! Tag: Sum of values in row if they also correspond to a month? Tag: 783586
  - 22
    - I need a formula that returns weekends and holidays for each mont I have created a mileage log with each month having its own spreadsheet in a workbook. The first sheet starts with February 2006 and the last sheet ends with february 2007. There are 12 sheets in total. Each month start on the first day and ends on the last day of that month. The calendar is South African. How can I create a formula that shows a Saturday, Sunday and Holiday per month. I have used =TEXT(A2, "ddd") to return days but I don't know how to create a formula that also returns holidays. Please help. Tag: Sum of values in row if they also correspond to a month? Tag: 783576
  - 23
    - Percentage of a Column of numbers I have a column of numbers and would like to know how many of those numbers make up 80% of the total of the column. Does anybody have a formula for that? Tag: Sum of values in row if they also correspond to a month? Tag: 783575
  - 24
    - where can I see my question and answer? Yesterday I ask a question I can't see my question, how can I find my question and answer Tag: Sum of values in row if they also correspond to a month? Tag: 783569
  - 25
    - Variance Report - Need to reflect 0% Hello! I need my variance number to show 0% even when the formula's answer is #DIV/0! Tag: Sum of values in row if they also correspond to a month? Tag: 783568
- Next
  - 1
    - Amortization schedule with periodic payments I have a loan, and the borrower is paying in an irregular fashion. Some months he pays and some he doesn't. How can I setup the amortization schedule to reflect this and add the appropriate interest? Thanks in advance ezy Tag: Sum of values in row if they also correspond to a month? Tag: 783564
  - 2
    - Disable Advanced Filter? Is there a way that I can disable the Advanced Filter option in a protected worksheet either through the UI or VBA? When I protect it I have the option to disable AutoFilter, but Advanced Filter is still available and I don't want users to have access to it. Thanks in advance! Kevin Tag: Sum of values in row if they also correspond to a month? Tag: 783561
  - 3
    - Copy Paste Wierdness Hello -- I have a Office Excel 2003 SP3. Here is the situation ... I have a Workbook that has many Worksheets that have some pretty intense formulas. I open a second instance of Excel with a blank Workbook. I highlight the whole worksheet and hit copy. I then Paste over on the new Blank Workbook ... and low and behold ... it is just copying the values (no formulas). If I perform the exact same test in the same instance of Excel it works fine. The funny thing is I am unable to reproduce this on other machines. Does anybody have any ideas of how this could be happening? Thanks, --Rich Tag: Sum of values in row if they also correspond to a month? Tag: 783560
  - 4
    - please, please help please, please help how do i get sheets, duplicated from a master sheet, to automatically number a field in each new opened sheet consequetive to the previous sheet. Tag: Sum of values in row if they also correspond to a month? Tag: 783559
  - 5
    - Share Workbook command grayed out on menu/unavailable, Why? I want to share the workbook I created but the command is grayed out on the menu. Why would this be grayed out, is there a step I need to do before I can share the workbook? Tag: Sum of values in row if they also correspond to a month? Tag: 783555
  - 6
    - Excel set up is right to left rather than left to right Office 2003 was installed in Hebrew. Excel reads from rt to left than left to right. i.e. columns originate in the rt and the rows are indentified by number on the rt rather than the left. How do I switch the format to English?? Tag: Sum of values in row if they also correspond to a month? Tag: 783551
  - 7
    - Limitations for Calculated Field formulas in Pivot Tables Is there a list or even a guideline on what excel formulas are allowed when creating a Calculated Field in Pivot Tables? For example, I want to use the TODAY() function in the pivot table, but I keep receiving errors. Is there a way to use a function that operates like the TODAY or NOW functions within the Calculated Fields? Thanks, Brandon Tag: Sum of values in row if they also correspond to a month? Tag: 783550
  - 8
    - ABC Report Hi, I have a spreadsheet with the total sales for one month on items that were sold and have been asked to do an ABC report based on this info. We want to see how many items make up 80%, 15% and 5% of the total sales for the month. Then categorize those items as A, B, and C items. Is there a formula that can designate each item as an A, B, or C item? Thanks, Tag: Sum of values in row if they also correspond to a month? Tag: 783547
  - 9
    - Excel 2003 colum does anyone know if it is possible to increase the number of colums in a single worksheet from 256 to 400 for excel 2003 ? Tag: Sum of values in row if they also correspond to a month? Tag: 783533
  - 10
    - Greater Than and less than HELP PLEASE Tried for hours to get this to work and ready to throw laptop out of window. What I'm trying to do : enter mileage into A1 For first 200 miles charge Â£0.40 per mile for miles above 200 Charge Â£0.25 per mile and enter combinded charge into A2. and if mileage is less than 200 in A1 carry balance (200-A1) over to B1 to increase B1 Â£0.40 rate i.e 200 plus balance from A1 Hope that makes sense . Many Thanks for any help. Tag: Sum of values in row if they also correspond to a month? Tag: 783532
  - 11
    - Display #'s incorrectly (format) =IF(AND(H3-O$1<0,M3-O$1<0,H3<>M3),"Past due "&-MAX(H3-O$1,M3-O$1),MAX(H3-O$1,M3-O$1,0)) Formula above works great (Thanks to those that helped), but the problem I'm now having is when account is not past due the other #'s return with serveral 0's. Example:00000 or 00221 I would like 221 to appear verses 00221 or 0 versas 0000 -- Elena Tag: Sum of values in row if they also correspond to a month? Tag: 783527
  - 12
    - 0 bytes I saved an excel 07 file that contained 300 kb of data yet, when tried to re-open get a message saying the file is corrupt or I have the wrong file extension. When I look at the files properties it now says 0 bytes!? Any ideas!? Andy Tag: Sum of values in row if they also correspond to a month? Tag: 783523
  - 13
    - linking info from word and excel I have a tool in excel spreadsheet that calculates scores and percentages. After the calculations are complete, I have to transfer(retype) the results and some of the info from that worksheet to a letter template in Word. In the end I have to send out both the worsheet and the letter together. Is there a way that I can combine both documents AND/OR have some of the info from the excel worksheet to link and automatically update to the Word letter template so that I don't have to send two separate documents and have manually type the info from excel to word? Any suggestion is appreciated. Tag: Sum of values in row if they also correspond to a month? Tag: 783516
  - 14
    - How do I print with the grids in place from my excel worksheet? I am usuing MS office 2003 and want to know how to print my spreadsheet with the grids intact. Tag: Sum of values in row if they also correspond to a month? Tag: 783515
  - 15
    - Convert Date Format to Minutes I have a spreadsheet from an outside source and the Minutes are formatted as a date, so in the formula bar it looks like this: 1/3/1900 10:00:00 PM but in the actual cell, it looks like this: 94:00:00. How can I get the cell to read just 94? Thanks!! Tag: Sum of values in row if they also correspond to a month? Tag: 783511
  - 16
    - Showing when a file was updated I am trying to figure out how to have a cell display when a file was last updated. Is there a way to make it show the most current date that any cell has been updated. I have tried using =TODAY() but that doesn't change with the most recent change. Tag: Sum of values in row if they also correspond to a month? Tag: 783508
  - 17
    - workday formula question I am trying to create a schedule for a machine. I know how much of a day each job will take. When I use a formula to just add column b to c, the result actually includes the hour of the day, but I need to exclude the weekend. When I use workday function then the fraction of the day seems to not be included in the calculation. Is there a way to combine these functions to add a portion of a day only using the work week? B C b+c workday function=WORKDAY(C3,B3) 3.25 5/1/08 12:00 AM 5/4/08 6:00 AM 5/6/08 12:00 AM Tag: Sum of values in row if they also correspond to a month? Tag: 783505
  - 18
    - Printing pages from Pivot Tables automatically I have created a Pivot table tracking expenses and quanities of items that each dept in our company has purchased for a given month. I want a new sheet to print for each dept. I know how to filter by dragging the dept number into the page area of the Pivot Table but I have to select each dept individually and print. Isnt there a way I can have excel automatically choose each individual dept and print their expenses on separate sheets? Thanks! Tag: Sum of values in row if they also correspond to a month? Tag: 783503
  - 19
    - Excel slow to save to network drive I am trying to troubleshoot a machine that is running XP Pro and Office 2003. It is extremely slow to save files to a network drive with Excel. Word is somewhat slow, but Excel is really bad. For example, it takes almost 60 seconds to save a 175K Excel file. The server is in the same building. I took a laptop down and plugged into the same ethernet hub and opened and saved the same file. It took only 3-4 seconds. The network in that office is somewhat slow, but still, there must be a problem with the machine taking 60 seconds to save. The problem doesn't seem to occur with local files. What could be causing this? Thanks, Chris Tag: Sum of values in row if they also correspond to a month? Tag: 783498
  - 20
    - Adding Shapes to Spreadsheets I want to add an autoshape to a spreadsheet, but when I go to Autoshapes , the selection choices are not highlighted. As a result , I am unable to select them. How do I get the choices to be highlighted / available for selection? Tag: Sum of values in row if they also correspond to a month? Tag: 783479
  - 21
    - Macro help I'm new to macros, could some explain how I can use this macro to allow spell check when a sheet is protected? Using a macro is the only work around that I know of. Sub Spell_Check() ActiveSheet.Unprotect Password:="justme" Cells.CheckSpelling SpellLang:=1033 ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Unprotects the sheet, does the spellcheck then reprotects the sheet. "justme" can be changed to your password. Gord Dibben MS Excel MVP On Sun, 10 Feb 2008 14:32:00 -0800, Brian <Brian@discussions.microsoft.com> wrote: >I made a spread sheet for work the has some cells protected then a password. >But once I enable the password, the spell check cannot be used. > >Any help would be appreciated Tag: Sum of values in row if they also correspond to a month? Tag: 783477
  - 22
    - Hide a column "if"? Hello, I have a workbook with 250 sheets. I need to be able to hide column F unless cell E2 has the value "Level 1". If there is another value in E2 or it is blank, I need column F to be hidden. I need this on all 250 sheets. Is this possible? Thanks in advance, you are all always to helpful! tgcali Tag: Sum of values in row if they also correspond to a month? Tag: 783458
  - 23
    - How do I compare 2 different Excel files for matching content Tag: Sum of values in row if they also correspond to a month? Tag: 783455
  - 24
    - Saving an Excel Workbook Dear all, Is it possible to alter a workbook so that when a user opens it he/she is automatically prompted to save it? Also I have a cost model in Excel that has a cover worksheet in the workbook. The cover sheet has a macro button that simply selects the next worksheet. Is it possible to create prompt window to remind the user to save? Thanks in advance, Neil Tag: Sum of values in row if they also correspond to a month? Tag: 783452
  - 25
    - Do you know how to send a single worksheet in Vista Excel? I want to send just one worksheet from an Excel workbook in the Vista version. I want to send it not as an attachment but as the body of the email. Any ideas? Thank you, Tag: Sum of values in row if they also correspond to a month? Tag: 783448

Hi, For each product I have the date it was checked, and a value for if there
are any imperfections, I want a formula that works out how many products
there were with imperfections in any month. Products with any imperfections
have a 1 in that box, each colum represents a product checked.

Month May May May June
Product has imperfection? 1 1 0 1

So I want my formula to return that May has 2 wrong products, June has 1...
I thought it was going to be a simple countif, but i've tried =COUNTIF(B2:E2,
B1:E1="May") and it's just returning a blank! Please help!! I've tried so
many variations of formulas and not getting anywhere!
Thanks
Ceri

Top

Re: Sum of values in row if they also correspond to a month? by Pete_UK

Pete_UK
Fri May 09 03:26:32 CDT 2008

Try it this way:

=3DSUMIF(B$1:E$1,"May",B2:E2)

Hope this helps.

Pete

On May 9, 9:13=A0am, ceri_m <ce...@discussions.microsoft.com> wrote:
> Hi, For each product I have the date it was checked, and a value for if th=
ere
> are any imperfections, I want a formula that works out how many products
> there were with imperfections in any month. Products with any imperfection=
s
> have a 1 in that box, each colum represents a product checked.
>
> Month =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
May =A0 =A0 May =A0 =A0 May =A0 =A0 June
> Product has imperfection? =A0 =A0 =A0 1 =A0 =A0 =A0 1 =A0 =A0 =A0 0 =A0 =
=A0 =A0 1
>
> So I want my formula to return that May has 2 wrong products, June has 1..=
.
> I thought it was going to be a simple countif, but i've tried =3DCOUNTIF(B=
2:E2,
> B1:E1=3D"May") and it's just returning a blank! Please help!! I've tried s=
o
> many variations of formulas and not getting anywhere!
> Thanks
> Ceri

Top