Seemingly complex #VALUE! data type mismatch error...

[sckuperman]

Greetings!

I searched throughout the forum for an answer to this error, but I cannot seem to hone in on exactly what is causing this to happen...

I have a complex workbook with many worksheets, and references to cells from several worksheets to others.

I have a worksheet named CONTROL that has cells that have the names of sales people as text. The first few cells are blank, as we change or delete sales people in these cells due to attrition, but that doesn't seem to be a problem in this case. In the formula with which I am having a problem, I directly reference each cell, and I do not think having a blank cell is causing the error that I am getting.

I have a worksheet named "MNS Deal Log". I cannot take the spaces out of the tab name because of the reference from other sheets that I cannot edit. This worksheet includes column "B" that has the names of sales people to check against my CONTROL worksheet. The cells in column "B" have data validation against the array in CONTROL to make sure that only those names are available for entry. Separately, cells in columns "I" through "N" have user entered dates as "m/d/yyyy".

I have a third worksheet named "Activity Calculator". "B8" includes COUNTing the total number of sales people listed in the array on CONTROL, taking out the blanks. There is a data validated cell at "A1" where I can select a "month year" from an array on CONTROL, such as "April 2016" (without the quotes, of course). What I need to accomplish is a formula that sums individual COUNTIFS greater than zero, to total the number of salespeople who have entered a date within the month/year of A1, anywhere in columns "I" through "N" on the "MNS Deal Log", so I can divide by B8 to get a percentage of sales people who have participated in a given A1 month. Please look at the following code:

 (SUM(
(IF((COUNTIFS('MNS Deal Log'!B:B,Control!BJ2,'MNS Deal Log'!I:N,">="&TEXT(A1,"m/d/yyyy"),'MNS Deal Log'!I:N,"<"&TEXT(EOMONTH(A1,0),"m/d/yyyy"))>0),1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ3)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ4)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ5)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ6)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ7)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ8)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ9)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ10)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ11)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ12)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ13)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ14)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ15)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ16)>0,1,0))+
(IF(COUNTIF('MNS Deal Log'!B2:B1001,Control!BJ17)>0,1,0))
)/B8)

When I just sum the COUNTIF instances that check for the existence of a sales person anywhere in the "MNS Deal Log", column "B", my formula works. This is true even though B2, for example, is currently blank

Here however, I edited the first instance of COUNTIF to try a COUNTIFS that first checks for a valid sales person but also includes checking to see if any A1 relevant date was entered anywhere in columns "I" through "N" on the "MNS Deal Log" (between the first and last of the month in A1).

The error I get is #VALUE! I've tried using TEXT, not using TEXT, using DATE, et al. I cannot seem to work through this error. I've tried stepping through the calculation and the very first step will return the error, so that doesn't help me. I realize there is a data mismatch involved, but i can't figure out which - or how to resolve.

Does anyone have any insight that might help?

THANKS!

 

[FDibbins]

Just a comment on this...

I have a worksheet named "MNS Deal Log". I cannot take the spaces out of the tab name because of the reference from other sheets that I cannot edit.

if you edit the sheet name in a workbook, all formulas that contain that sheet name should auto-update with that new name

(will read more of what you wrote)

 

[FDibbins]

Not sure if this is pertinent, but your countifs() uses full columns, while your countif() uses smaller ranges? (seeing as you are just summing, should not matter)

Instead of that big SUM formula, consider using a helper cell in each of those rows, put the countif in that, and then summing those?

 

[StephenCrump]

You're passing different dimensions to COUNTIFS:

COUNTIFS('MNS Deal Log'!B:B,Control!BJ2,'MNS Deal Log'!I:N,">="&TEXT(A1,"m/d/yyyy"),'MNS Deal Log'!I:N,"<"&TEXT(EOMONTH(A1,0),"m/d/yyyy"))

I'm also not sure why you're comparing dates wrapped in a TEXT function?

 

[StephenCrump]

I'm also not sure why you're comparing dates wrapped in a TEXT function?

OK, now that I read your post properly, it looks like you were experimenting to try to make it work.

 

[sckuperman]

Hi,

Thanks - but it won't auto update from a different workbook where that workbook needs the same info when using data from ANOTHER workbook that needs the original name...

Confusing, yes. But I cannot change the name of the tab!

 

[sckuperman]

You're passing different dimensions to COUNTIFS:

COUNTIFS('MNS Deal Log'!B:B,Control!BJ2,'MNS Deal Log'!I:N,">="&TEXT(A1,"m/d/yyyy"),'MNS Deal Log'!I:N,"<"&TEXT(EOMONTH(A1,0),"m/d/yyyy"))

I'm also not sure why you're comparing dates wrapped in a TEXT function?

Should the different dimensions matter? The scope of the second and third criteria differ from the scope of the first criteria.

Thanks,

 

[sckuperman]

By the way, when I change the Text and EOMONTH critera to:

">"&EOMONTH(A1,-1)

and

"<"&EOMONTH(A1,0)+1)

When I evaluate the formula and begin stepping through it, it shows

(SUM((IF(COUNTIFS('MNS Deal Log'!$B:$B,0,'MNS Deal Log'!$I:$N,">42460",''MNS Deal Log'!!$I:$N,"<42491")>0,1,0))+...

Notice the underlined part and the italics. Different variations with TEXT and DATE show appropriately formatted dates to compare against the lookup criteria, but otherwise result in the same evaluation - so I cannot for the life of me figure out what is missing here...

 

[StephenCrump]

Should the different dimensions matter?

COUNTIFS has been set up to do a column-wise comparison.

For example, if you test two ranges with three columns each, COUNTIFS will count

- matches for criteria1 in criteria_range1 column 1 AND criteria2 in criteria_range2 column 1, plus
- matches for criteria1 in criteria_range1 column 2 AND criteria2 in criteria_range2 column 2
- matches for criteria1 in criteria_range1 column 3 AND criteria2 in criteria_range2 column 3

Presumably you want to see criteria 2 satisfied in at least one of the columns (I:N in your case), which is not the same thing.

 

[sckuperman]

COUNTIFS has been set up to do a column-wise comparison.

For example, if you test two ranges with three columns each, COUNTIFS will count

- matches for criteria1 in criteria_range1 column 1 AND criteria2 in criteria_range2 column 1, plus
- matches for criteria1 in criteria_range1 column 2 AND criteria2 in criteria_range2 column 2
- matches for criteria1 in criteria_range1 column 3 AND criteria2 in criteria_range2 column 3

Presumably you want to see criteria 2 satisfied in at least one of the columns (I:N in your case), which is not the same thing.

Wow! Great catch. Didn't know that was the case. Your presumption is correct. Knowing what I'm looking to do, do you know how it should it be phrased in the function?