Sumif Function Returns Zeros |
Sumif Function Returns Zeros - Excel |
|
I am using the SUMIF to sum a column from a separate worksheet into the active worksheet. It returns 0 even though the criteria matches values in the range. I have attached a screen print of the fx function window. Can anyone tell me why it won't pull back the sum in the sum_range of 43212.91?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
Similar Topics
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?
=SUMIF(D3:D13,"criteria",D3:D13)
I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)
It might just be that I don't know what this means in Help:
"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).
Can anyone help?
I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
FORMULA: =sumif(range,criteria,sum_range)
I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15
because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?
All help is appreciated.
Thank you.
So I've got some data, which has the approximate form of a sine function. I want to find all the x-axis intercepts. I tried using the intercept function and swapping around the y values for the x values, but it only returns 1 value (so I'd guess it uses a linear regression to estimate a single line through the axis).
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
I was thinking of trying a nested if/and statement but I haven't quite figured out how to do it. Basically I want to identify the two values where it switches from positive to negative and also indentify the values where it goes negative to positive, I can then fit a straight line between them to find a better approximation of the intercept (though it might not be necessary). Preferably I'd like it all one function as I'm not doing it in VBA (I might do later though, we'll see).
Can anyone suggest how I'd find these value or the x-intercept. Any help would be greatly appreciated.
Hi all.
I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.
I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.
I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:
By repeating this code for each worksheet, I can make each one be zoomed just right.
However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.
I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.
Anyone know how to do this ?
I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.
I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.
I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:
Sheets("WELCOME").Select
Range("A1:N18").Select
ActiveWindow.Zoom = True
By repeating this code for each worksheet, I can make each one be zoomed just right.
However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.
I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.
Anyone know how to do this ?
Entering time values in custom format [h]:mm:ss
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
Cells accept hours over 23,
Adding cells in column returns correct total time.
Have not found a way to multiply these cells by a $ hourly rate.
So use HOUR(cell ref) and MINUTE(cell ref) to capture values in referenced
cell - then use these values to calculate total payment for $rate per hour.
However, the HOUR(cell ref) formula returns the hours in excess of 24 when
the cell contains an hour value in excess of 23 (ie 27 hours returns 3).
I am trying to work out how to calculate the geometric mean of a series of values, some of which are negative, ie. investment returns over a series of years with some negatives.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
The Geomean function doesn't work because it only uses positive numbers.
Any help would be greatly appreciated.
I have entered a vlookup formula in a cell and it returns the correct value from the other worksheet. However when I copy the formula down it keeps returning the exact same value as the first cell. If I enter a formula in each individual cell it returns the correct value. Its driving me crazy ! I woyld be glad of any advice
Many Thanks
Karamazov
Many Thanks
Karamazov
I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database.
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference.
I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN).
What modifications do I make to this to get this to work per above requirements?
Sub CopyMe()
Dim SaveMeAs As String
SaveMeAs = Sheets("Sheet1").Range("B2").Text
Sheets("Sheet3").Copy
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & SaveMeAs
End Sub
I have a textbox in my userform where anything typed in the box is entered into cell A2 in my worksheet. However, multiple lines of text are not properly displayed in my worksheet. I have enabled MultiLine and EnterKeyBehaviour in the properties window of the textbox so I can type multiple lines in my textbox. The problem is that when I press the button to enter the text from my txtbox to cell A2, the cell doesn't actually display the text in separate lines. It just places a square symbol in the place that enter should have been pressed and the text should have been split into separate lines.
How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..
How can I get the separate lines of text in my textbox to be properly displayed in a cell? Thanks for your help in advance..
I have created an excel spreadsheet to track attendance of an exercise class I am teaching. I would like to know each person's current percent attendance. (so if we have had 5 classes and they have attended 4 of those classes then it should show 80%). I would like it to keep a running tab, so as I add in the person's attendace it will continue that count, up to the 26 classes that are being offered. I thought if I did a sumif/countif I could get it to work, but it doesn't. Here is what I tried: =sumif($e$4:$ad:4,">0")/countif($e$4:$ad$4,">1"). Do you have a suggestion that would work? Thanks!
Column 1 has roughly 20 rows of information. Cell C1 has the formula =A1.
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...
I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values
Thanks
Is there an Excel guru that can help with this - its related to "drop down menus"
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
I have 2 colombs of data.
AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.
At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1
The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.
How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...
ANy suggestions??
Hi there, I have a string of numbers and I would like to add zeros to the end of it.
For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).
I would do it manually but I have a column of over 2000 different numbers.
Thanks for your help, you guys are great!
For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).
I would do it manually but I have a column of over 2000 different numbers.
Thanks for your help, you guys are great!
If you change Excel to Full Screen View and your taskbar is always on top (as
it normally is), the bottom of the spreadsheet is hidden behind the taskbar.
Does anyone know how to get around this without hiding the taskbar? I have
found that if I drag the taskbar to the bottom and then back up again, the
Excel window resizes itself correctly and the bottom of the spreadsheet is
visible again. Problem is, I want to do this from VBA! Can anyone help
please???
it normally is), the bottom of the spreadsheet is hidden behind the taskbar.
Does anyone know how to get around this without hiding the taskbar? I have
found that if I drag the taskbar to the bottom and then back up again, the
Excel window resizes itself correctly and the bottom of the spreadsheet is
visible again. Problem is, I want to do this from VBA! Can anyone help
please???
Is there a worksheet function that will generate all possible combinations of
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
Hi everyone....this is my first post here ....and not my last I believe!
My question I believe is easily solvable for you cracks of excel.
I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
I created an example on a worksheet attached just for you to understand.
I apreciate all the help I can get.
Thank you so much.
RG
My question I believe is easily solvable for you cracks of excel.
I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
I created an example on a worksheet attached just for you to understand.
I apreciate all the help I can get.
Thank you so much.
RG
Posted this on the Ozgrid forums, but haven't gotten any help yet, so I thought I'd try here too!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
I've been having a strange problem lately. I have a fairly lengthy macro that works perfectly most of the time. Occasionally it will run as expected but as soon as the macro ends, excel becomes unresponsive to mouse-clicks. When I click anywhere (trying to select a cell, or an excel menu item...clicking anywhere in excel) I'll get the a 'ding' system sound and nothing will happen. BUT, if I use the keyboard arrow keys, I can see that the active cell selection moves accordingly. Then it gets really strange - when I have a cell highlighted and press any key to input text, it gets duplicated. So if I press "s' it will input "ss" into the cell, and then when I press enter it will auto-move to the next cell down, but nothing ends up getting saved into the previous cell.
I don't understand what's going on at all. I can't think of anything in my macro that would have these kinds of effects. I've made sure that screenupdating is turned back on at the end of every procedure. If I go into the VBE, I can manually run procedures and they all work fine. The only way I've been able to get back to normal is by force closing excel altogether and re-opening. Any ideas?
______________________
Still having trouble, and the same thing is happening with this workbook on two different computers, so I don't think it's a hardware, or OS specific issue. When it gets locked up like this, I can still do anything in the VBE (edits cells, run macros, etc.) with no problems. If I'm in the excel window, I can click alt on the keyboard and the shortcut keys for the menu come up, but I can't go deeper than that by clicking the letter shortcuts, they do nothing. If I use the delete key to delete the contents of a cell, then it gets deleted. But if I type anything else (numbers, letters, or symbols) then it types 2 instances of the key every time, yet when I hit enter, nothing changes in the cell. I also can't really bring focus to the excel window if something else (ie. the VBE) is on top of it, clicking into the excel window just gives the little system 'ding' sound and nothing happens.
If I hit the save button in the VBE (since I can't click anything in the excel window), then it seems to snap out of it and go back to normal. I tried searching for anything simmilar to this and can't find anything...
Any help would be much appreciated. This is driving me nuts!
Good day... I need an IF Function that will allow me to action a time in a time range:
... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty
Any help is appreciated.
... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty
Any help is appreciated.
Is there any way to automatically lock in the date after you pull it up with the TODAY function? Or is there another function that will do what I'm trying to do?
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
I want it to automatically fill in today's date, when a certain empty cell has a value put in, then freeze there.
Thanks in advance!
Hello, please help me out with this one.
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!
So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!!