Help using OFFSET, MATCH and COUNTIFS in Data Validation List

Hi everyone,

I have the following formula in a data validation list and it works great but I need to add additional criteria to the COUNTIF and I can't seem to make COUNTIFS work with it.

Formula:

Please Login or Register  to view this content.

I don't understand why COUNTIFS isn't working as I've tried to use it in the example below.

Formula:

Please Login or Register  to view this content.

The reason I need the additional COUNTIF criteria is so I don't end up with both Active and Achieved goals listed.

Thanks very much for any help you can provide.

If that is the actual formula then: COUNTIFS

Thanks for your reply, protonleah.

I tried COUNTIFS but it isn't working. That's why I posted...

Hi,

It's quite difficult to determine where the problem lies without seeing the actual spreadsheet in question, or at least a dummy version which illustrates the same issue.

Regards

I've got it figured out. With the help of a few helper columns on both of the sheets used in the formula and using two MATCH criteria instead of needing to use two COUNTIF criteria, I was able to make it work as needed using the following formula.

Formula:

Please Login or Register  to view this content.

The problem was mostly related to data type mismatch using General or Text formatting while the formulas were referencing numerical data.

The helper columns are formatted as Numbers so using two Match criteria, one being the name of the participant and the other using one of the helper columns and then COUNTIF also referencing the same helper column, the Data Validation lists populate with the correct data.

XOR LX - Sorry, I figured I was just putting the formula together wrong and that someone familiar with using OFFSET with MATCH and COUNTIF or COUNTIFS would easily see where I went wrong. It really seems to me that COUNTIFS might not be able to work in a DV list at all.

I've attached a copy of what I'm working on so you can see what it was I was doing. The Data Validation list is on the Transactions sheet in cells located in column I and uses a helper column on the Goals sheet in column B, Goals_Achieved_Helper as shown in the formula above. The helper columns on the Transaction sheet in columns A and Q work together in order to provide correct numbers for the reference of the helper in column Q in the DV list formula.

This is a work in progress so there is still much left to do, mostly with setting up macros and formatting on the various sheets.