Using a data validation drop-down list with countifs formulas

[ngelbke]

I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error. Any suggestions?

 

[T. Valko]

I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error. Any suggestions?

Post the formula.

 

[XLS-LENT]

I have a table created that uses countifs formulas to pull data. I want the data to change based by client - so I created a drop down list with each client's name, and thought I could add a criteria to the countifs formula based on the client's name in the cell- but I get a value error. Any suggestions?

Agree with T.Valko, but for what it's worth.

A typical (trying to be a mind reader here) user error would be not putting quotes around your criteria (assuming your hard coding the clients name in your formula)

Maybe we could have a crack at it with SUMPPRODUCT?

 

[ngelbke]

Now that I had a night of sleep - I realize my question is very vague. And, unsure if even possible. Here is my formula below:

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9)

This pulls all late workorders

Now, I want to take a step further - and select late workorders by client. Please see example formula below:

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491, "Altisource Portfolio Solutions")

Now, how can I do this using a data validation drop down list to signal it to change formulas? The only other option I can think of is to manually record macros and record changing each formula and using buttons with the clients' name on them. But, I thought I would seek an easier solution.

Any help will be greatly appreciated.

Thanks,

 

[Skybluekid]

Try :

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491, (Data Validation Cell))

 

[T. Valko]

Now that I had a night of sleep - I realize my question is very vague. And, unsure if even possible. Here is my formula below:

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9)

This pulls all late workorders

Now, I want to take a step further - and select late workorders by client. Please see example formula below:

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491, "Altisource Portfolio Solutions")

Now, how can I do this using a data validation drop down list to signal it to change formulas? The only other option I can think of is to manually record macros and record changing each formula and using buttons with the clients' name on them. But, I thought I would seek an easier solution.

Any help will be greatly appreciated.

Thanks,

If I understand what you're asking, I think all you need to do is replace:

"Altisource Portfolio Solutions"

With the cell reference of your drop down list:

A1 = drop down list

=COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491,A1)

 

[ngelbke]

Sometimes I just overthink things. That definitely worked. The only issue is if I want to go back to pull all the lates - instead of client specific. I have "all" in my drop down menu - but don't know what to add to go back to list all late workorders. I added a column with the word all, but I came back with 0 (probably because it was checking both criteria. Any suggestions?

 

[T. Valko]

Sometimes I just overthink things. That definitely worked. The only issue is if I want to go back to pull all the lates - instead of client specific. I have "all" in my drop down menu - but don't know what to add to go back to list all late workorders. I added a column with the word all, but I came back with 0 (probably because it was checking both criteria. Any suggestions?

Maybe this...

=IF(A1="All",COUNTIF('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,COUNTIFS('All Lates - Data'!F2:F491,'Late Workorder Breakdown'!A9,'All Lates - Data'!$C$2:$C$491,A1))

 

[ngelbke]

Works great! Thank you very much!

 

[T. Valko]

Works great! Thank you very much!

You're welcome. Thanks for the feedback!