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!

 

[StephenCrump]

Knowing what I'm looking to do, do you know how it should it be phrased in the function?

Sorry, still not clear. Can you perhaps post a simplified screen shot to illustrate your layout and intended results.

At the moment, it's not clear why you're using COUNTIFS with the criterion:=BJ2, but then adding one to the count if each of BJ3, BJ4, ... BJ17 appear anywhere in the relevant column?

 

[sckuperman]

Sorry, still not clear. Can you perhaps post a simplified screen shot to illustrate your layout and intended results.

At the moment, it's not clear why you're using COUNTIFS with the criterion:=BJ2, but then adding one to the count if each of BJ3, BJ4, ... BJ17 appear anywhere in the relevant column?

Hi, I don't know how to post a screen shot of what I'm trying to do, but please let me make this clearer:

I need to check if a salesperson's name ('Control'!BJ#) is entered in column B of 'MNS Deal Log'; and if that salesperson exists in a cell of that column B, then I need to search that particular row for any dates entered in columns I through N that fall between the first and last of the month specified by the month/year entered in A1 of the current worksheet. If ALL of these criteria are met (name exists in B, and at least one date exists in one of the cells from columns I through N of 'MNS Deal Log'), then I need to tally a 1.

Ultimately, I need to count all of the 1's and zeroes that are calculated by parsing though all of the 'Control!BJ#'s' from 2 to 17, representing each salesperson who participated that month - so I can divide by the total number of sales people and get a ratio formatted as a percentage.

I hope this is clearer.

 

[StephenCrump]

If I'm understanding correctly, I think you're looking for something like this:

D2: {=SUM(--(ISNUMBER(MATCH(ROW(INDIRECT("1:"&ROWS(MasterNameList))),IF(MMULT((MONTH(DataDates)=MONTH(RequiredDate))*(YEAR(DataDates)=YEAR(RequiredDate)),TRANSPOSE(COLUMN(DataDates)^0)),1,0)*MATCH(DataNames,MasterNameList,),))))} Array-entered

where in this case:

MasterNameList is A2:A7
RequiredDate is D1
DataNames is C6:C17
DataDates is D6:I17

Count shown = 5, i.e. for Name1, Name2, Name4, Name5 and Name6 who each have one or more sale dates in March 2016.

	A	B	C	D	E	F	G	H	I
1	MasterList		Date	Mar 2016
2	Name1		Count	5
3	Name2
4	Name3		DATA
5	Name4			Sale1	Sale2	Sale3	Sale4	Sale5	Sale6
6	Name5		Name1	18 Jan 16	06 Mar 16	12 Feb 16	05 Jan 16
7	Name6		Name2	31 Jan 16	03 Jan 16	15 Mar 16	14 Jan 16	06 Dec 15	22 Dec 15
8			Name3	17 Jan 16	01 Jan 16
9			Name4	12 Jan 16	21 Dec 15	19 Feb 16	20 Feb 16	27 Dec 15	04 Dec 15
10			Name5	28 Mar 16
11			Name6	01 Mar 16	26 Jan 16	31 Dec 15	01 Jan 16	21 Feb 16
12			Name1	20 Dec 15	28 Dec 15	22 Feb 16
13			Name2	07 Mar 16	10 Jan 16	09 Mar 16	18 Jan 16	21 Feb 16	14 Feb 16
14			Name3	10 Jan 16	11 Jan 16	05 Feb 16	03 Jan 16	14 Jan 16
15			Name4	09 Mar 16	18 Feb 16	29 Jan 16	15 Dec 15	04 Dec 15	20 Mar 16
16			Name5	14 Feb 16	29 Dec 15	27 Feb 16
17			Name6	29 Dec 15	01 Jan 16	20 Jan 16	10 Mar 16

<tbody>
</tbody>

​

 

[sckuperman]

If I'm understanding correctly, I think you're looking for something like this:

D2: {=SUM(--(ISNUMBER(MATCH(ROW(INDIRECT("1:"&ROWS(MasterNameList))),IF(MMULT((MONTH(DataDates)=MONTH(RequiredDate))*(YEAR(DataDates)=YEAR(RequiredDate)),TRANSPOSE(COLUMN(DataDates)^0)),1,0)*MATCH(DataNames,MasterNameList,),))))} Array-entered

where in this case:

MasterNameList is A2:A7
RequiredDate is D1
DataNames is C6:C17
DataDates is D6:I17

Count shown = 5, i.e. for Name1, Name2, Name4, Name5 and Name6 who each have one or more sale dates in March 2016.

	A	B	C	D	E	F	G	H	I
1	MasterList		Date	Mar 2016
2	Name1		Count	5
3	Name2
4	Name3		DATA
5	Name4			Sale1	Sale2	Sale3	Sale4	Sale5	Sale6
6	Name5		Name1	18 Jan 16	06 Mar 16	12 Feb 16	05 Jan 16
7	Name6		Name2	31 Jan 16	03 Jan 16	15 Mar 16	14 Jan 16	06 Dec 15	22 Dec 15
8			Name3	17 Jan 16	01 Jan 16
9			Name4	12 Jan 16	21 Dec 15	19 Feb 16	20 Feb 16	27 Dec 15	04 Dec 15
10			Name5	28 Mar 16
11			Name6	01 Mar 16	26 Jan 16	31 Dec 15	01 Jan 16	21 Feb 16
12			Name1	20 Dec 15	28 Dec 15	22 Feb 16
13			Name2	07 Mar 16	10 Jan 16	09 Mar 16	18 Jan 16	21 Feb 16	14 Feb 16
14			Name3	10 Jan 16	11 Jan 16	05 Feb 16	03 Jan 16	14 Jan 16
15			Name4	09 Mar 16	18 Feb 16	29 Jan 16	15 Dec 15	04 Dec 15	20 Mar 16
16			Name5	14 Feb 16	29 Dec 15	27 Feb 16
17			Name6	29 Dec 15	01 Jan 16	20 Jan 16	10 Mar 16

<tbody>
</tbody>

Okay... Newb question, but how did you just post an example as above in tabular format??? I'll need to know that for the future!

Also, incredibly insightful. You hit the nail on the head. I thought about what would go into developing an array formula for this need but wasn't even sure where to start - and you come up with transposing data and MMULT, which is brilliant, so in advance, "thank you"... The really scary part is that I understand your function and it really is a brilliant way to include all of the elements to solve the equation.

However, while it appears to work in your example it doesn't seem to work with my real data. I'm not getting any errors that I can detect. It simply comes up with zero as the result. Interestingly enough, when I "Evaluate Formula" and step through it, there is some verbiage in the bottom of the popup window that I've never read before when evaluating formulas. It reads, "A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not." Wow.

I entered your formula as an array function (CSE), without the brackets as shown, as:

{=SUM(--(ISNUMBER(MATCH(ROW(INDIRECT("1:"&ROWS(salesprof))),IF(MMULT((MONTH('MNS Deal Log'!I:N)=MONTH(A1))*(YEAR('MNS Deal Log'!I:N)=YEAR(A1)),TRANSPOSE(COLUMN('MNS Deal Log'!I:N)^0)),1,0)*MATCH('MNS Deal Log'!B:B,salesprof,),))))}

...where "salesprof" is a named array for Control!BJ2:BJ17, where the sales people's names exist in a column on another worksheet in the same workbook; 'MNS Deal Log'!I:N represents the columns where the data dates exist; the "required date" as A1; and the "data names" are in column 'MNS Deal Log'!B:B. The dates in my "data dates" cells are formatted as dates of type: *3/14/2012, if it makes any difference. Names in both "master name list" and "data names" are formatted as General. "Required Date" is also formatted as General. Could any of this be the culprit? Do I need to change the formatting of any elements?

 

[sckuperman]

Also, I just re-Evaluate Formula'ed, and adjusted the scope from 'MNS Deal Log'!I:N to 'MNS Deal Log'!I2:N30sed within the array, just to make it faster and more manageable, and dispersed throughout the MMULT array are some #VALUE! elements, which of course also exist after solving for True/False, and then zeroes and ones in that and subsequent steps, leaving "IF(#VALUE!,1,0) in the equation, which becomes ...#VALUE!*MATCH..., which becomes #NA!'s, which eventually solves for zero. I looked at the cell that refers to the first instance of #VALUE! and it APPEARS to be empty. It is formatted for date like all of the other cells, and I 'deleted' the cell again for good measure. Why would it throw this error? Why would any other cell in the matrix?

If the Evaluation shows ...MMULT(({1,4,4,6,1,1;1,6,6,6,1,1;1,1,#VALUE!,1,7,8;

And the array being reference against MONTH in MMULT is I2:N300, which cell is represented by the error?

This is a doozy!

 

[sckuperman]

Okay... I know I reply a lot with updates, but...

I picked through every cell in the array and found manual errors. How frigging frustrating. After deleting these non-date entries, your formula works like a charm. I'm thinking of finding a way to tweak your function just a bit to somehow ignore errors or ignore non-dates or convert non-dates to blanks or zeroes.

Regardless, THANK YOU. THIS WORKED LIKE A CHARM.

 

[StephenCrump]

How did you just post an example as above in tabular format??? I'll need to know that for the future!

Part B in this link gives you a couple of ways to post screenshots: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Interestingly enough, when I "Evaluate Formula" and step through it, there is some verbiage in the bottom of the popup window that I've never read before when evaluating formulas. It reads, "A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not."

Not a problem. The message means that we're using a so-called "volatile" function, INDIRECT(), so the result you see when using Evaluate Formula may not be the final result in the spreadsheet. Volatile functions are those that re-calculate every time Excel calculates. For this reason, they're generally to be avoided if possible, or at least used sparingly.

I've rearranged my example slightly and put in some intermediate steps to better illustrate how the formula is working:

K6:K17 =MMULT((MONTH(D6:I17)=MONTH(D1))*(YEAR(D6:I17)=YEAR(D1)),{1;1;1;1;1;1}) Array-entered,
= Number of March sales highlighted

L6:L17 =MATCH(C6:C17,MasterNameList,) Array-entered, =position of name in MasterList, e.g. Name1 is in row 6

M6: =IF(K6,L6,0) Copy down, = same as column L if one or more sales in column K

N6:N11 =MATCH({1;2;3;4;5;6},M6:M17,) Array-entered, shows that 1,2,3,5 and 6 exist in column M, but not 4.
Count of N6:N11 is 5 = result shown in D2.

However, while it appears to work in your example it doesn't seem to work with my real data ....

Just curious, did you get it to work on my dummy data and replicate my result?

The problems with your data could be formatting and/or date issues. Easy to spot in the actual workbook, but hard to diagnose via messaging back and forth. Any chance you could upload a sample workbook (no sensitive data of course)? To do this you'll need to use a provider like box.com, and post the link here.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	MasterList		Date	Mar 2016
2	Name6		Count	5
3	Name2
4	Name3		DATA								Buildup …..
5	Name4			Sale1	Sale2	Sale3	Sale4	Sale5	Sale6		No of Sales	Name row#	Name row # if Sale	Match
6	Name5		Name1	18 Jan 16	06 Mar 16	12 Feb 16	05 Jan 16				1	6	6	6
7	Name1		Name2	31 Jan 16	03 Jan 16	15 Mar 16	14 Jan 16	06 Dec 15	22 Dec 15		1	2	2	2
8			Name3	17 Jan 16	01 Jan 16						0	3	0	8
9			Name4	12 Jan 16	21 Dec 15	19 Feb 16	20 Feb 16	27 Dec 15	04 Dec 15		0	4	0	#N/A
10			Name5	28 Mar 16							1	5	5	5
11			Name6	01 Mar 16	26 Jan 16	31 Dec 15	01 Jan 16	21 Feb 16			1	1	1	1
12			Name4	20 Dec 15	28 Dec 15	22 Feb 16					0	4	0
13			Name3	07 Mar 16	10 Jan 16	09 Mar 16	18 Jan 16	21 Feb 16	14 Mar 16		3	3	3
14			Name1	10 Jan 16	11 Jan 16	05 Feb 16	03 Jan 16	14 Jan 16			0	6	0
15			Name6	09 Mar 16	18 Feb 16	29 Jan 16	15 Dec 15	04 Dec 15	20 Mar 16		2	1	1
16			Name5	14 Feb 16	29 Dec 15	27 Feb 16					0	5	0
17			Name2	29 Dec 15	01 Jan 16	20 Jan 16	10 Mar 16				1	2	2

<tbody>
</tbody>

​

 

[StephenCrump]

I picked through every cell in the array and found manual errors. How frigging frustrating. After deleting these non-date entries, your formula works like a charm. I'm thinking of finding a way to tweak your function just a bit to somehow ignore errors or ignore non-dates or convert non-dates to blanks or zeroes.

Regardless, THANK YOU. THIS WORKED LIKE A CHARM.

You're welcome.

I'm glad you were able to debug the problem. Does this help:

=SUM(--(ISNUMBER(MATCH(ROW(INDIRECT("1:"&ROWS(MasterNameList))),IF(MMULT(IFERROR((MONTH(DataDates)=MONTH(RequiredDate))*(YEAR(DataDates)=YEAR(RequiredDate)),0),TRANSPOSE(COLUMN(DataDates)^0)),1,0)*MATCH(DataNames,MasterNameList,),))))

 

[sckuperman]

You're welcome.

I'm glad you were able to debug the problem. Does this help:

=SUM(--(ISNUMBER(MATCH(ROW(INDIRECT("1:"&ROWS(MasterNameList))),IF(MMULT(IFERROR((MONTH(DataDates)=MONTH(RequiredDate))*(YEAR(DataDates)=YEAR(RequiredDate)),0),TRANSPOSE(COLUMN(DataDates)^0)),1,0)*MATCH(DataNames,MasterNameList,),))))

Thanks!

I wasn't sure if IFERROR was the right path to follow, if that were to work, for example - but seeing your breakdown helps explain it as well. That works like a charm!

Thanks again!

 

[sckuperman]

Last question, seeing as we're on a roll...

Suppose I wanted to limit the initial MATCH to only those on the MasterNameList that correspond to a sales_manager that is listed in the matching row cells in the column immediately to the right, next to each sales person's name on the MasterName List?

In this way, if I changed, say "C1" to have the name of a sales manager, I could have the formula MATCH INDIRECTly only to those ROWS that include DataNames that first MATCH only to the INDEX that refers to their sales manager's name in C1?