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]

Do you mean like this (building on the previous example):

E3: {=SUM((ManagerList=RequiredManager)*(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,),))))}

ManagerList =B2:B7
RequiredManager: =E2

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

<tbody>
</tbody>

​

By the way, I have been meaning to ask: originally you said you were looking for the percentage of sales people who "participated" in a given month. Does the absence of a March sale mean that a sales person did not participate, or could it be that they did participate but didn't secure any sale?

 

[sckuperman]

Wow. Just wow.

Here is the final array formula with my (almost insignificant compared to yours) edits including named ranges:

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

Thus...

If C1 on the main worksheet is blank, with C1 representing a cell to select either a sales team manager or a blank entry from our Control worksheet's lookup table, then we initially want to total the whole branch office by summing all of the individual sales professionals in the entire branch who contributed in a given month, of such month that is represented by the month and year selected from the lookup table in A1. To get this total, we check to see if each sales professional entered any dates on our MNS Deal Log worksheet that fall only within the month chosen in our main worksheet's cell A1, where the dates they will enter will be entered in a cell on any of their populated row entries where from columns "I" to "N" ('MNS Deal Log'!I:N): 1. that they first met with a new prospective customer, 2. and/or reported doing a profile, 3. and/or reported having completed an assessment, 4. and/or presented a proposal, 5. and/or closed a sale, 6. and/or started our finance team on billing a new customer. If they did enter any "A1"-related dates in this fashion, they are counted as part of the total number of participating sales professionals for that given "A1" month. We then divide that sum by the total number of sales professionals in the entire branch to get a ratio expressed as a percentage.

If C1 on the main worksheet shows a sales team manager, we follow the aforementioned initial logic, however we filter the selected participating sales professionals by the team they are on represented by their manager (C1). We then divide THAT sum by the total number of sales professionals on their sales manager's TEAM, to represent a ratio of participating sales professionals by team.

WHEW!

AND A PARTRIDGE IN A PEAR TREE.

THANKS!