I have a numbers spread sheet, col A date, col D number 1. I have to collect the number of days (1’s) for 12 month period – Jan 1 to Dec 31. I am getting an error message that says not formatted correctly. I can attach the file of the spreadsheet if that helps.
—–
Rich Clark
MacMost Q&A Forum • View All Forum Questions • Ask a Question
How Do I Format SUMIFS Function In a Numbers Spread Sheet?
Comments: 7 Responses to “How Do I Format SUMIFS Function In a Numbers Spread Sheet?”
Comments Closed.
SUMIF and similar functions can be tricky to use with times. Typically, you are asking that if a cell meets conditions. So if a cell is equal to a value, greater than a value or less than a value.
I don't know what your current formula looks like (would have been helpful to include it). But if sounds like you want to check to see if the date is both less than a value and greater than a value. So less then Dec 31, 2017 and greater than Jan 1, 2017. Right?
So you are doing the right thing by using SUMIFS (with an S) instead of SUMIF. You need to test for two conditions. An example would be:
SUMIFS(D,A,">=1/1/2017",A,"<=12/31/2017") This will check to see if the date in column A is both greater than or equal to the first of the year and less than or equal to the last of the year. Another way to do it would be to simply have another column, say B, that used the YEAR function to extract the year from column A. Then use SUMIF to test to see if the year is =2017. You can hide column B if you wish.
SUMIFS($D$11:$D$5123,"=1",$C$11:$C$5123,"≥1/1/2016",$C$11:$D$5123,"≤12/31/2016")
Column C is the date column and column D is where I record a 1 if we are in the US. I need to calculate the days for 3 separate annual periods, 2015, 2016, 2017, and this changes each year and each accumulation rolls forward a year at a time. I had this formula working last year, but due to an interrupted IOS upgrade in Dec that required a full system erase and reinstall, I lost all my files including this one.
I can see for starters that you are using the wrong parameters for SUMIFS. Check the help text on this. It is: SUMIFS(sum-values, test-values, condition, test-values…, condition…). So you have what looks like the test-values and conditions reversed. Plus a range that goes from C11 to D5123 (so it includes 2 columns!). Also, I would try to use full columns for this. What do you have in rows 1-10? Are they header rows? If so, then you can just the the columns:
SUMIFS(D,D,"=1",C,"≥1/1/2016",C,"≤12/31/2016")
But if your intention is to count the number of rows, then consider using COUNTIFS instead of SUMIFS. The only reason SUMIFS will work at all is that you happen to have a column that is 0 or 1 anyway. So you are both using it as a condition and the sum.
SUMIFS(($D$11:$D$5123,"=1",$C$11:$C$5123,"≥1/1/2017",$C$11:$C$5123,"≤12/31/2017")) The sum values which I thought go first are in col D, and the amount to be added is a 1. So I want to add the 1's if the dates are between Jan 1& Dec 31 for a year. The test values, dates, are in column C.Rows 1-10 are where I place the numbers that result from the sumifs, and then they are used in another formula. Is there a way for me to send the whole file and then you can see?
Rich: Read the definition for SUMIFS in Numbers carefully. Look at the examples. Pay special attention to the parameters and what each does. Then start with a fresh new SUMIFS formula based on that. Or, as I suggested, look at COUNTIFS instead, as I think that is what you really want.
Gary
I had this SUMIFS formula working on this spreadsheet last year. But I lost the files when I was doing an IOS upgrade and the WIFI was interrupted and I had to have the Apple store in San Antonio do an erase and reload. So I have had to rebuild from my notes which are obviously not as exact as I need them. I have checked the examples and my format is exactly as the examples as far as I can determine. However I will check the count one.
Rich
Rich: Your format is definitely NOT right.
The formula you are using is:
SUMIFS(($D$11:$D$5123,”=1″,$C$11:$C$5123,”≥1/1/2017″,$C$11:$C$5123,”≤12/31/2017″)
But the parameters for SUMIFS are:
SUMIFS(sum-values, test-values, condition, test-values…, condition…)
The second parameter is a range of test values. However, in your formula your second parameter is "=1" which is a condition. The first parameter needs to be the range representing the sum value. The second parameter needs to be the range for the first test. Then third parameter needs to be the condition of the first test. Then it is range and condition, range and condition in pairs after that.
Perhaps you are looking at SUMIF instead of SUMIFS?