Formula help for countifs with multiple criteria

Marcela Hernandez

Hello,

I would really appreciate some help crafting a formula for a dashboard. Thank you in advance for your help!

Goal: count based on current year

Criteria: field type (lead time) within the current year (request date) referencing a different worksheet

Formula attempt:

=COUNTIFS({Lead Time}, HAS(@cell, Type@row), AND({Request Date}, >=DATE(2020, 1, 1), {Request Date}, <=DATE(2020, 12, 31)))

Result: #Invalid Data Type

Would like to also leverage for the summation of costs based on current year.

Kelly Moore

Hey @Marcela Hernandez

Only a few tweaks were needed. If we use the YEAR function, we can designate the year which will eliminate the need for inclusive date range. Date functions (Year, Month, etc) can cause errors so the IFERROR helps with that.

Also, without seeing your dataset, it wasn't clear if you needed the HAS function or not. Are you working with multi-select dropdowns? If not, we can just use Type@row to find the data.

I'll give you formulas with and without the HAS function

=COUNTIFS({Lead Time}, HAS(@cell, Type@row), {Request Date}, IFERROR(YEAR(@cell),0)=2020)

No HAS()

=COUNTIFS({Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)

Does this work for you? If yes, to use for Costs, you would use SUMIFS. If the criteria is still the same, you would add whatever range you wanted Summed (Cost?) in addition to all the terms used in your COUNTIFS. =SUMIFS({your cost range}, {Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)

PS. If you did need the inclusive date range the formula would look like this. If you're using AND function, it is part of the criteria portion, not the range portion of the formula.

=COUNTIFS({Lead Time}, Type@row, {Request Date}, AND(>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)))

cheers

Kelly Moore

Hey @Marcela Hernandez

Only a few tweaks were needed. If we use the YEAR function, we can designate the year which will eliminate the need for inclusive date range. Date functions (Year, Month, etc) can cause errors so the IFERROR helps with that.

Also, without seeing your dataset, it wasn't clear if you needed the HAS function or not. Are you working with multi-select dropdowns? If not, we can just use Type@row to find the data.

I'll give you formulas with and without the HAS function

=COUNTIFS({Lead Time}, HAS(@cell, Type@row), {Request Date}, IFERROR(YEAR(@cell),0)=2020)

No HAS()

=COUNTIFS({Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)

Does this work for you? If yes, to use for Costs, you would use SUMIFS. If the criteria is still the same, you would add whatever range you wanted Summed (Cost?) in addition to all the terms used in your COUNTIFS. =SUMIFS({your cost range}, {Lead Time}, Type@row, {Request Date}, IFERROR(YEAR(@cell),0)=2020)

PS. If you did need the inclusive date range the formula would look like this. If you're using AND function, it is part of the criteria portion, not the range portion of the formula.

=COUNTIFS({Lead Time}, Type@row, {Request Date}, AND(>=DATE(2020, 1, 1), <=DATE(2020, 12, 31)))

cheers

Marcela Hernandez

Thank you so much @KDM! Worked perfect!

The HAS function was suggested to me by Smartsheet for multi-select lists when I need to pull counts.