Excel 2010 :: Sumifs Formula Not Working?
Jun 10, 2013
I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies
ADVERTISEMENT
Feb 15, 2013
Excel 2010
I have the following SUMIFS formula that produces the desired results until I get the the last qualifier, displayed in red:
=SUMIFS(JBHAMOUNT,JBHMONTH,C18,BNSFCATEGORY,B20,BNSFLOCATION,"Alliance")
The result of this formula is: #VALUE!
View 2 Replies
View Related
Nov 13, 2013
SUMIFS with less than & more than
I found this thread which works for normal ranges
I want to Sum for Values Greater than a specific date in my formula.
This first one works for a specific date (Relevant reference in red), so say 01/01/2014
Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
Code:
To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])
View 4 Replies
View Related
Jun 25, 2014
In excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
View 7 Replies
View Related
Mar 20, 2014
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies
View Related
Jan 2, 2014
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
View 13 Replies
View Related
Jul 31, 2012
I'm trying to sum over a range (column A) based on values in another range (column B). Column B's cell values are calculated with a simple numeric formula that is either a subtraction or an addition of two cell references. I want to sum all the column A cells for which the corresponding column B cells are determined by a subtraction. Then separately, sum the cells for which the corresponding column B cells hold an addition. It seems that SUMIFS is the function to use, but how do I specify the sum criteria as "a subtraction" or "an addition"?
Windows 7, Excel 2007
View 5 Replies
View Related
Aug 14, 2013
I have a spreadsheet in version Excel 2007 that is refreshing data from Access tables. Users are needing charts to show progress by month, but are also needing to filter by area and one to filter by a section.
What I have so far is the data being calculated using SUMIFS so that I can pull between dates. I then added two comboboxes so that the user could filter by an area and by section. This is working, except that I have not been able to figure out how to allow the user to select "All" so that they can see all data from on or both of the selections.
View 2 Replies
View Related
Apr 8, 2013
I am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
View 12 Replies
View Related
Dec 10, 2012
In Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies
View Related
Jul 12, 2013
I am using Excel 2010 on Windows 7.
I copied (with 'Paste Link') rows from a master Excel file to a file that performs calculations. This file (and many others like it) need to be modifed by other people. My tester has informed me that the data is not updating between the files for her, although it does work for me.
When I check 'Data->Edit Links' it shows the status of the file as 'unknown'. If I click 'Check status' it is then OK, but the next time I look at it, it has reverted to 'unknown'.
1. How do I get the status to remain at OK?
2. Why do the links work for me, but not for my end users?
View 2 Replies
View Related
Apr 29, 2014
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
[Code] .....
Untitled.png
View 10 Replies
View Related
Oct 24, 2011
I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
View 5 Replies
View Related
Sep 20, 2013
I am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.
Option Explicit
Sub alex_it_saving_lives()
'get the image folder path
Dim strImagePath As String
strImagePath = GetImageFolderPath
If (Len(strImagePath) = 0) Then
[Code] .........
View 3 Replies
View Related
Dec 12, 2013
In an Excel Pro Plus 2010 workbook on a Windows 7 PC, trying to use the "After" argument in a VBA .Find function and it's not working as expected.
I've got the following data in column D:
Row1: Title
Row2: Row2Data
Row3: Row3Data
And using this simple procedure:
Sub FindTest()
If Range("D:D").Find("Title", After:=Range("D1")) Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print Range("D:D").Find("Title", After:=Range("D1")).Row
End If
End Sub
My expecation is that "Not Found"will be returned, but instead I'm getting the row # (1).
If I change the value in D1 the procudure returns "Not Found", as expected.
If I change the find argument to look for "Row2Data" it returns 2.
I also tried changing the "After" argument to "D2" ... same result.
Finally, I tried using this format: "Range("D:D").Find("Title", [D2])" and got the same result.
View 2 Replies
View Related
Apr 19, 2013
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
View 3 Replies
View Related
Oct 17, 2013
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
View 1 Replies
View Related
Nov 8, 2012
I have a long bit of code that at one point saves a Word document and then saves it again with a new name and the old (legacy) .doc extension.
This all works find when the user is running Office 2007. However, it errors out for using Office 2010. The reference libraries are all correct (as far as I know).
Code:
Dim myDoc As Word.Document
Dim saveAsName as String
saveAsName = "some text here" + ".doc"
myDoc.Save
myDoc.SaveAs fileName:=(saveAsName), FileFormat:=wdFormatDocument
View 9 Replies
View Related
Jan 8, 2013
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
View 2 Replies
View Related
Jan 15, 2013
I'm working on a macro that does the following:
1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values
- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:
I'm running Windows 7, Office 2010
Code:
'Chart variables
Sheets("Summary").Select
Dim ChartRange As Range
[Code]....
View 4 Replies
View Related
Apr 5, 2013
I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)
The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.
When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line
Error Message: Unable to set the Visable Property of the Pivot Item Class
Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#
[Code]....
View 9 Replies
View Related
Sep 7, 2010
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
View 4 Replies
View Related
Nov 23, 2011
I am trying to hide the excel 2010 application, open a csv file on a network drive, show the excel application.
The problem is a "Downloading" progress indicator appears, during the workbook open event I assume.
Code:
Sub Macro1 ()
Application.Visible = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="S:LOGT5.CSV"
Application.Visible = True
Application.DisplayAlerts = True
I have seen the similar progress indicator during the "Save" event too. How to hide this?
View 3 Replies
View Related
Aug 14, 2013
I am trying to create an equations that sums up a range based on matched dates. I have considered using an array, but that turned out to be much more work than necessary.
I have never used the SUMIFS before and having trouble, here is the equation I'm using.
=SUMIFS($F$2:$F$204238,MONTH(E2:E204238),"=month(referenced_range)")
*F2:F204238 is the range that needs to be summed up
*the month(referenced range) is in date form
*I was able to pull and evaluate all the data on it's own, but when using it in the sumif equation it gave me zero result so it must be a syntax error.
View 3 Replies
View Related
Oct 10, 2009
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:
If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)
I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.
BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B $ 278 4AustriaExternalNoneExt3C $ 900 5AustriaExternal-8A $ 2,388 6NorwayInternal-MV $ 567 7AfricaExternal-SY $ 1,234 8AfricaInternalNoneIntJN $ 908 9TOTAL $ 6,275
Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)
And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.
Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.
View 9 Replies
View Related
Dec 6, 2012
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
View 1 Replies
View Related
May 7, 2014
I've been looking all over for the most basic of VBA codes to insert a timestamp in a single cell (B1) when cell A1 changes due to formula result change. All the answers I've found are for manual updates of A1.
A1 has the simple formula: =SUM(F1:F10000)/3. I would like cell B1 to insert a new timestamp when the results of this formula in A1 change. On a weekl basis, I will paste-value data into the whole F column, which will change the resultes in A1.
If this can't be done, or is too complicated (I don't really write VBA, only copy and paste basic code), is it possible to have a timestamp inserted into B1 based on the paste-value event into the F column?
Excel 2010
View 2 Replies
View Related
Mar 14, 2012
I am using Excel 2010 .I have set up Data validation for a dropdown box so I can select from a list of items. In the old versions of Excel the actual drop down arrow used to appear in each cell. In the version I have, the drop down arrow only appears when you select the actual cell. When I did the validation I checked the " In-Cell Dropdown", but it still doesnt put the arrow in the cell. Is this functionality available in Excel 2010 ?
My second issue is a formula.
The last name is in a list of items and users have to select Yes or No to theitems on the list. I am wanting to create another spreadsheet that automatically populates based on their responses.
In short, I want to be able to set up a rule or formula that states if the answer in column A is "y" then I need the information in column B to be displayed.
The ultimate aim is to get a automatic sub set, (in another tab), of the orginal information based on users responses.
View 2 Replies
View Related
Aug 11, 2013
I have Excel 2010. I am creating a spreadsheet for server backups. I have always been told to take the current size of the disk and multiply it by 10% to figure out the backup size needed for the backup. I am trying to create a formula to do this. I have a server with 25GB. The formula i tried was
=a1*10%. A1 being 25gb. Excel does not like the GB in the cell also this is only giving me the difference. I need to know what size to allow for my backup on this server.
View 9 Replies
View Related
May 21, 2014
I'm trying to create a formula that states. If tank 1 maximum capacity is less than 500,000 and the difference in inventory is greater than the absolute value of 10% or 20,000 or if the tanks maximum capacity is between 500,000 and 1,000,000 and the difference in inventory is greater than the absolute value of 20% or 100,000 or if the maximum tank capacity is greater than 1,000,000 or the difference in inventory is greater than the absolute value of 30% or 300,000 then Y OR N.
I figured out the absolute value portion of the formula but I'm not really sure how to combine that with the tank maximum capacity piece.
=IF(OR(ABS(T14)>10%,S14>20000),"Y","N")
View 7 Replies
View Related
