# SUMIFS Formula Wrong Result

1. ## SUMIFS Formula Wrong Result

Hello,

This is pretty basic, and although I should be up to the task, for some reason I cannot figure it out.

In the attached file (tab: "LOBSTER - YEAR" cell: C4), I am using the following formula:
Formula:
`Please Login or Register  to view this content.`

That seems to work just fine, but when I use the same formula in cell D4, it gives me the wrong result (\$0 vs. \$450):
Formula:
`Please Login or Register  to view this content.`

Any help would be greatly appreciated.

Thank you.

2. ## Re: SUMIFS Formula Wrong Result

There is no data in your file, so it is difficult to see where it is going wrong. Please re-submit with some numbers in, and indicate what you would expect the output values to be.

Pete

3. ## Re: SUMIFS Formula Wrong Result

I entered data for only the cells involved to test the formula. I indicated that the result should be 450 rather than 0.

4. ## Re: SUMIFS Formula Wrong Result

It doesn't work because of your use of merged cells, which are an abomination & should be avoided like the plague.
Your formula is looking at col C for SSR & col D for \$\$, hence there is no value. Get rid of the merged cells & put SSR into D2 & it will work.

5. ## Re: SUMIFS Formula Wrong Result

Thanks so much Fluff13!

6. ## Re: SUMIFS Formula Wrong Result

You can use this formula in C4:

=IF(\$B4="","",SUMIFS('LOBSTER - MONTH'!C4:\$GH4,'LOBSTER - MONTH'!C\$2:\$GH\$2,'LOBSTER - YEAR'!C\$2,'LOBSTER - MONTH'!C\$3:\$GH\$3,'LOBSTER - YEAR'!C\$3))

and this one in D4:

=IF(\$B4="","",SUMIFS('LOBSTER - MONTH'!D4:GH4,'LOBSTER - MONTH'!C\$2:GG\$2,C\$2,'LOBSTER - MONTH'!D\$3:GH\$3,D\$3))

Note that instead of using full-column references, I have changed these so that they can be offset by one cell. You can then copy the 2 cells and paste them into the next 2 cells, and so on.

I would suggest that you use this formula in o4:

=SUMIFS(C4:M4,C3:M3,O\$3)

which can then be copied into P4, and this will avoid errors caused by explicitly trying to add "" to other "" cells.

Hope this helps.

Pete

7. ## Re: SUMIFS Formula Wrong Result

Thanks so much Fluff13!
Glad to help & thanks for the feedback.

8. ## Re: SUMIFS Formula Wrong Result

No feedback for me !!

Pete

9. ## Re: SUMIFS Formula Wrong Result

Hi Pete,

Thank you so much for that suggestion!

I understand Fluff13's remarks about merged cells, but in trying to make my spreadsheets easier to use/navigate, I do end-up using merged cells. I understand that it complicates things when it comes to formulas and references, but it is extra work I deem acceptable in return for sheet design and end user navigation.

Given this, your solution is of great help, but I am trying to figure out why yours works and mine didn't.

Is it simply down to changing the references from full-column references (4:4) to specific references (D4:GH4)? If this is the case, would it be too much to ask to elaborate why the merged cell is problematic for the full-column references and not the other?

Thanks so much.

10. ## Re: SUMIFS Formula Wrong Result

Originally Posted by Pete_UK
No feedback for me !!

Pete
Sorry Pete, was in the middle of studying your response & leaving you feedback when you posted.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1