Excel: Using "And" condition comparing derived values

Question

I have a spread sheet that has multiple divisions going down in rows, each row has one person and relevant information for the employees. I have used a formula to eliminate all the duplicate divisions. I am trying to create summary information from the divisions.

My division were consolidated with the formula =INDEX($F$2:$F$587,MATCH(0,COUNTIF($J$1:J1,$F$2:$F$587),0))

Now I would like to use the derived value the corresponding Cell J2:J21 and count the row IF the value in F2:F588 is equal the value in the J cell AND the value in H2:H588 is equal to the value in Q1

This goal is to count how many persons in the organization are AMIND and total place the total number in the division.. I have tried a number or permutation but cannot seem to get it to work.
enter image description here

Answer 1

If I understood your question correctly what you want is:
(1) unique values of Column F are listed in Column J
(2) so first you want to match Column F to the values in Column J
(3) then match corresponding Column H values to Cells N1, O1, P1, Q1, ....
(4) for (2) and (3) conditions get the count under Cells N1, O1, P1, Q1, ....

If my understanding is correct then enter the following formula in Cell N1:

=COUNTIFS($F$2:$F$587,$J2,$H$2:$H$587,N$1)

and drag/copy this formula across and down as required.

See image below (with data till row 25):