Identical formulas, One works -the other won't What is wrong ?

**Fred Houck** · 11-28-2023, 02:43 PM

I know it is normally requested that a sample spreadsheet be provided however those I'm working on are large and multiple making it very hard to provide a proper example of a spreadsheet. In lue of that I have created a work document showing two formulas both of which are in the cells and the only differences between them are who data is being looking at and which cell the data is to compare the result to. I've struggles with this #CALC error for 7 hours at this point and cannot for the life of me figure out what is wrong. Hopefully someone will take a look at what I've provided and be able to determine what the problem is for me.

Thank you for trying in advance, I'm simply burnt out trying to figure it out.

**AliGW** · 11-28-2023, 02:46 PM

I think you might need (Individual #3) and you need to check it for a leading space, by the looks of it.

**Fred Houck** · 11-28-2023, 03:00 PM

Thank you for taking a look at my issue, However the way the wording is i.e. Individual #3 is exactly
how it is the drop selection list on the table the formula is looking at.

INDIVIDUAL NAME DROPDOWN SELECTION BOX
(Husband)
(Wife)
Individual #3
Individual #4
Individual #5
Individual #6
Individual #7

**AliGW** · 11-28-2023, 03:06 PM

Not in the screenshot in the document attached, though.

**Fred Houck** · 11-28-2023, 03:22 PM

AliGW yes you are correct the screen shot is merely the tab and column that the formulas are in to do the calculations. The descriptive name you see on line 22
is just a description only.

The actual data the formula is looking at on a separate tab table has the selections to choose from that you see in the second message, those are
exactly how they are on that table sheet.

NOTE: AliGW I have created a short version of the spreadsheet that has the information I'm having problems with a formula discussed prior for you or anyone for that matter to look at and see if you can determine the problem since I cannot.

The formulas from the other sheet have been copied to this sheet which actually is where the data comes from and the formula still gives the same result. I have changed the name from Individual #3 to Person -3 but still get the same result.

Also I have noticed as of yesterday I have a sheet with a formula that till yesterday showed the formulas resulting answer - now it wont regardless of what I try to fix it. The cell will only show the formula
as text as long as you remove the equals sign in front of it. Have you ever come across this situation before?

Fred H

**JeteMc** · 11-30-2023, 12:44 PM

In the logical_test part of the formula there is: ...($C$7:$C$67)="Person 3"...
In the Value_if_true part of the formula there is: ...($C$7:$C$67)="Person -3"...
Change the Logical_test part to "Person -3" also.
BTW, Excel reports a circular reference in cell C95. I believe the formula there should be: =SUMPRODUCT(($B$7:$B$67=$C$94)*(MONTH($B$7:$B$67)=MONTH($C$94))*$E$7:$E$67)
Let us know if you have any questions.

**Fred Houck** · 11-30-2023, 02:15 PM

Hi,
Thanks for pointing that type-o for me. I did make that change so both now say "Person -3" however I'm still left with the same as yet
unsolved problem. I noticed when I open the formula window to inspect the formula I keep regardless of where I put parentheses I get
#Value! or Empty Arrays Not Permitted Errors.

This issue makes no sense considering the other two rows looking for the same answers using the same formula work flawlessly !

Back to the drawing board.

**JohnTopley** · 11-30-2023, 02:49 PM

One - not related - error is

Formula:

Please Login or Register  to view this content.

should be

Formula:

Please Login or Register  to view this content.

The incorrect formula only "works" because MONTH(n) always returns 1 and you are interested in month1 !

I suspect the #VALUE! error is because there is no data for "Person -3" in January; I don't have 365 so I cannot test the formula.

"old_fashioned" (much simpler) solution

Please Login or Register  to view this content.

**Fred Houck** · 12-01-2023, 03:37 PM

OMG !!!!!!!!!!!!!!!!!!
John - Your solution has made my problem go away

I can't thank you enough, I have been struggling with this specific formula for days -literally from the time I got up till 11:30 at night trying all kinds of way to write it to no avail. I guess there's a point where your so frazzeled you can't do anything right.

Thank you so much - now I can move on to other issue's...

**JohnTopley** · 12-01-2023, 03:45 PM

To avoid "frazzled brain", please get to the forum much earlier- after a few hours of trying, never mind days!

Anyway, glad it is resolved

Identical formulas, One works -the other won't What is wrong ?

LinkBack

Thread Tools

Rate This Thread

Display

Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Re: Identical formulas, One works -the other won't What is wrong ?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sumproduct works on the wrong column

Two "identical" array formulas, one works but the other doesn't, please help!

[SOLVED] Identical Formulas from Identical Data Sets Return Different Results

Formula works but for wrong date, please help

Looping works wrong (VBA compare)

One Macro Works - second identical in function - overwrites

Macro works, identical Function doesn't....why?

Bookmarks

Bookmarks

Posting Permissions