Hello all, thank you for taking a look at this. I am using Excel for Mac 2011, OS X Yosemite.
I am setting up a sales tracking database for my bakery. The data is entered daily, and I am building weekly and monthly summary sheets. Each product (e.g. Chocolate Chip Cookie) is a row. Each column is the number of products that were sold at a certain price on a certain day.
I am using =SUMIFS to look over each row and tell me how many cookies were made during a given date range AND at what price they were sold. We sell products at full price, then reduced price, then they are given free to employees. We track the product based on the day it was baked -- meaning cookies that are baked on Monday will be reduced on Tuesday and given away free on Thursday -- but they are always "Monday's cookies".
The problem I am having is this... My SUMIFS formula works GREAT in my first column, which is the column tracking the total number of cookies that were made. I was very excited to have gotten it figured out. But when I went to use the same formula in the next column, to track the reduced price cookies, it will only return a zero value.
By changing only the criteria 1 (from "Redu" to "Made") the formula again calculates the correct answer. So I know it must be an issue with criteria. But I cannot figure out what the issue is. I have searched the webs and this forum for several hours and cannot find any leads on this problem.
I'm having trouble posting the table to this thread, but here are two examples of the formulas I'm using, the first example is the column that is working great, the second example is the column that is returning the zero value.
=SUMIFS('Pastry DAILY'!$B5:$AC5,'Pastry DAILY'!$B$3:$AC$3,"Made",'Pastry DAILY'!$B$1:$AC$1,">="&$B$2,'Pastry DAILY'!$B$1:$AC$1,"<="&$D$2)
So "Pastry Daily" is the sheet where the daily numbers are stored, the sum range is the row containing all of the possible counts, the first criteria range contains the row that describes whether the count is Made, Reduced, or Free. The first criteria I have tried as a cell reference or text string, both work fine. The second and third criteria and ranges are to look at only certain dates, again those do not seem to be a problem.
So here is the formula in the next column that is returning a zero value. As you'll see, they are identical except for the first criteria.
=SUMIFS('Pastry DAILY'!$B5:$AC5,'Pastry DAILY'!$B$3:$AC$3,"Redu",'Pastry DAILY'!$B$1:$AC$1,">="&$B$2,'Pastry DAILY'!$B$1:$AC$1,"<="&$D$2)
It should be returning a value of 27, based on the data in the daily sheets.
I have checked the spelling of "Redu" and it is identical on both sheets -- there are no leading or trailing spaces.
Sorry I have not posted a sample table, but after reading the FAQ, I was still unable to figure out how to do it from a Mac.
Again thank you for taking the time to look at this, and for any help you can offer.
I am setting up a sales tracking database for my bakery. The data is entered daily, and I am building weekly and monthly summary sheets. Each product (e.g. Chocolate Chip Cookie) is a row. Each column is the number of products that were sold at a certain price on a certain day.
I am using =SUMIFS to look over each row and tell me how many cookies were made during a given date range AND at what price they were sold. We sell products at full price, then reduced price, then they are given free to employees. We track the product based on the day it was baked -- meaning cookies that are baked on Monday will be reduced on Tuesday and given away free on Thursday -- but they are always "Monday's cookies".
The problem I am having is this... My SUMIFS formula works GREAT in my first column, which is the column tracking the total number of cookies that were made. I was very excited to have gotten it figured out. But when I went to use the same formula in the next column, to track the reduced price cookies, it will only return a zero value.
By changing only the criteria 1 (from "Redu" to "Made") the formula again calculates the correct answer. So I know it must be an issue with criteria. But I cannot figure out what the issue is. I have searched the webs and this forum for several hours and cannot find any leads on this problem.
I'm having trouble posting the table to this thread, but here are two examples of the formulas I'm using, the first example is the column that is working great, the second example is the column that is returning the zero value.
=SUMIFS('Pastry DAILY'!$B5:$AC5,'Pastry DAILY'!$B$3:$AC$3,"Made",'Pastry DAILY'!$B$1:$AC$1,">="&$B$2,'Pastry DAILY'!$B$1:$AC$1,"<="&$D$2)
So "Pastry Daily" is the sheet where the daily numbers are stored, the sum range is the row containing all of the possible counts, the first criteria range contains the row that describes whether the count is Made, Reduced, or Free. The first criteria I have tried as a cell reference or text string, both work fine. The second and third criteria and ranges are to look at only certain dates, again those do not seem to be a problem.
So here is the formula in the next column that is returning a zero value. As you'll see, they are identical except for the first criteria.
=SUMIFS('Pastry DAILY'!$B5:$AC5,'Pastry DAILY'!$B$3:$AC$3,"Redu",'Pastry DAILY'!$B$1:$AC$1,">="&$B$2,'Pastry DAILY'!$B$1:$AC$1,"<="&$D$2)
It should be returning a value of 27, based on the data in the daily sheets.
I have checked the spelling of "Redu" and it is identical on both sheets -- there are no leading or trailing spaces.
Sorry I have not posted a sample table, but after reading the FAQ, I was still unable to figure out how to do it from a Mac.
Again thank you for taking the time to look at this, and for any help you can offer.
