Conditional formula inconsistently producing #VALUE error

[seh0872]

his one is hard to explain. I'll try with screenshots as best I can.

Below is my spreadsheet in progress of. being built. Notice the highlighted formula, which populates the cell based on the dropdown in cell C4. (For reference, this blue table is on worksheet "Stats")


The formula is counting if:

• cells within the range "Bets_Type" = the corresponding data in col A
• cells within the range "Bets_Result" = "Win" (cell V9)
• cells within the range "Bets_Ref_Week" = the value of the dropdown list, in this case, "Last Week"

The corresponding portion of the dataset this formula is looking to (the portion related to the range "Bets_Ref_Week" is below. (For reference, this data is on worksheet "Bets")


The dropdown options in Stats C4 contain a list of all options in any of the four time columns -- Day, Week, Month, or Year.

The cell data is producing correctly when selecting any day option ("Today" or "Yesterday") or any week option ("This Week" or "Last Week"). But when selecting any option in the Month or Year columns, the cell returns a #VALUE error. See below.


I am completely baffled.

Using the Formula Builder, I can see that the #VALUE error appears to be caused by the second COUNTIFS element ("Bets_Result,V$9). See below.


While this seems helpful, its really just adding to the confusion, because if I return to a selection for which the data is appearing correctly, the same value error shows in the Formula Builder, yet the cell is correctly populated and no #VALUE error appears.

I'm stumped.

Any help in trying to figure this one out?

While you're working on this one, I'll drop some screenshots for my other open threads.

Thanks all.

 

[CSmith]

Since this is calculated on what looks like a named cell/range then you are most likely getting an error based on prior calculation/range.