sumifs formula works in one column but only gives zero value in the next column

[rbziegler]

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.

 

[rbziegler]

=SUMIFS('Pastry DAILY'!$B5:$AC5,'Pastry DAILY'!$B$3:$AC$3,"*Redu*")
Also yields 27, the expected result. However I still need the formula to eventually search all columns (B5:BBB5) and sum only the results for the desired date range.

Thanks everyone for the help so far.

 

[Marcelo Branco]

It seems that no cells in columns where the condition
'Pastry DAILY'!$B$3:$AC$3, "Redu"
is TRUE, satisfies the two date conditions.

M.

 

[Aladin Akyurek]

=COUNTIFS('Pastry DAILY'!$B$3:$AC$3,"Redu")

yields a result of 7. Strange right?

And this?

=COUNTIFS('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)

A non-zero count would imply that there is non-zero value to sum in the sum range.

 

[rbziegler]

It seems that no cells in columns where the condition
'Pastry DAILY'!$B$3:$AC$3, "Redu"
is TRUE, satisfies the two date conditions.

M.

YES!! Thank you Aladin and Marcelo, you guys are truly MVPs. The issue was with the merged cells containing the date information. Because Excel only saw 'Pastry DAILY'!B2, it doesn't recognize the merged cell as including C2,D2,E2, it was simply stating as Marcelo said and Aladin's test formulas showed, that there were no valid dates in columns c,d, or e.

To workaround, I simply inserted a header row with the date in each column as Excel wants, and changed the criteria to search that row instead of the user-friendly merged row. It works perfectly and I can continue building the sheet now. I can simply hide the header row once the sheet is finished to maintain a clean look.

THANKS SO MUCH!! I really appreciate the time guys.