Complex formula problem

[anna82marie]

Good morning!

I may have over-stretched myself with a training matrix that I have created. I have figured out the formulas to return the number of red, amber, green based on the validity period for ALL staff, but can't for the life of me amend the formula to give me the results for a specific member of staff, selected via drop down.

The formula I am using for the total of expired certifications is:

=IF(J$3="N/A","",IF(J$3=Data!$A$25,COUNTIFS(J$9:J$1001,">"&Data!$C$25,J$9:J$1001,"<="&Data!$C$32),IF(J$3=Data!$A$26,COUNTIFS(J$9:J$1001,">"&Data!$C$26,J$9:J$1001,"<="&Data!$C$33),IF(J$3=Data!$A$27,COUNTIFS(J$9:J$1001,">"&Data!$C$27,J$9:J$1001,"<="&Data!$C$34),IF(J$3=Data!$A$28,COUNTIFS(J$9:J$1001,">"&Data!$C$28,J$9:J$1001,"<="&Data!$C$35),IF(J$3=Data!$A$29,COUNTIFS(J$9:J$1001,">"&Data!$C$29,J$9:J$1001,"<="&Data!$C$36),IF(J$3=Data!$A$30,COUNTIFS(J$9:J$1001,">"&Data!$C$30,J$9:J$1001,"<="&Data!$C$37))))))))

Running from a data sheet providing the dates the certificate needed to be taken between.


Any ideas? Please help save my sanity!

 

[BlueHornet]

I can give you some guidance, maybe, but I doubt if I'll be able to fully resolve this. Maybe what I will tell you can be enough.

In the first place, your formula is giving you "totals" of the unfiltered data. When you filter the data—which is what I presume you mean by 'selected via drop down'—then your DISPLAY might change, but the totals don't change. So try using the SUBTOTAL function (with its various parameters, as you'll see in the wizard when you start to enter it) which will show your filtered data.

Aside from that, you can make your formula much MUCH easier to audit and troubleshoot.

For example, you use the term COUNTIFS( J$9:J$1001) so frequently that you'd be better off making this a named range or a helper cell that you can more easily (more readably) substitute into the formula. You'll be able to do something similar with the SUBTOTAL function, I expect, as you get used to it.

Alternatively, you could make some of the COUNTIF comparisons directly on the sheet, on the applicable rows (even if you then hide the rows containing those formulas), and then simply refer to the results of the pre-made calculations in, again, much shorter and easier to comprehend formulas.