Search code examples
excelexcel-formula

Excel returns #VALUE! error when I use INDIRECT

I'm new to Excel and I'm writing an Excel formula that counts the employees state across the entire year( for example: how many times he had a vacation donated as letter V) and I managed to write this formula

 =SUMPRODUCT( COUNTIFS( INDIRECT("'" & {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"} & "'!$A$11:$A$38"), $B2, INDIRECT("'" & {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"} & "'!$B$11:$AF$38"), "V" ) )

and in the employees sheet it start with id then the name and then an empty cell for the state(as shown in the sample table below)

ID Name Vacation
1 Adam 0

in this code I'm looking for the state of V (Vacation) for the first employee located in cell B2. I have 12 worksheets for months. Each sheet is similar to the other in terms of the location of employees' names and the cells for the state of the day.

In each month's sheet, the names go from A11 to A38. And the area for the states is from B11 to AF38( in this square).a sample table is below

ID Name Day-1
1 Adam 0
2 John 0

In the employees statistics sheet from A2 to A23 is for the IDs. And from B2 to B23 is for the names. And from C2 to V24 is the area for each state each employee taking a row of course

I added a name tag ( or whatever it's called) from the names manager. to use them in the formula as you can see. but when ever i use the formula it says in the cell #VALUE!. I've tried to evaluate the formula and see where is the bug but I just couldn't see where the issue is. the furthers i managed to come is to tell that the issue is around (INDIRECT) function,but i don't know how to solve it.

Solution

  • There are lots of problems in your formula.

    First, you just can't put two criteria ranges with different dimension in COUNTIFS. that is, name range is A11:A38 (1-D array), but state range is B11:AF38 (2-D array). the two ranges have different shapes, and that won't work in COUNTIFS.

    Second, INDIRECT doesn't work with arrays such as {"JAN!A11:A38", FEB!A11:A38"}.

    I believe what you need is 3D reference instead: https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

    I hope you have Office365.

    Because with Office365, you can use 3D reference together with VSTACK to consolidate your 12 month's data into one table:

    =VSTACK(JAN:FEB!A11:C13)

    enter image description here

    Then you can use below to count vacations for "John":

    =SUM(--(FILTER(B2:C7,A2:A7="John","")="V")).

    =FILTER(B2:C7,A2:A7="John","") filters out data for "John".

    =SUM(--(FILTER(B2:C7)="V") count the number of state = "V".

    Edited:

    For easier implementation, you can put =VSTACK(JAN:FEB!$A$11:$C$38) in name manager. (I named it "Consolidated"). (Make sure you use absolute reference. i.e do not omit the dollar sign).

    enter image description here

    To get all employee names in 12months data, use =TAKE(Consolidated,,1). To get all the states column in 12months data, use =DROP(Consolidated,,1).

    So to filter out data for an employee, use =FILTER(DROP(Consolidated,,1),TAKE(Consolidated,,1)=B2,"")

    enter image description here