Share via

How do I display the value from the rows that qualified using a countif function?

Anonymous
2020-05-06T22:08:17+00:00

I used a countif function to return the number of cells that fell in a specified date range. Now, I'd like to know the value that is in the cell that qualified in the date range. Any help on how to write the formula is GREATLY appreciated!!

Here's my CountIf formula:

=COUNTIFS('May Invoice Forecast'!$D$6:'May Invoice Forecast'!$D$44, "Direct", 'May Invoice Forecast'!$B$6:'May Invoice Forecast'!$B$44, ">=2020-01-01", 'May Invoice Forecast'!$B$6:'May Invoice Forecast'!$B$44, "<=2020-04-10")

If a cell was counted using the above formula, now I want to display the value (which is a name) that is in the range of cells 'May Invoice Forecast'!$A$6:'May Invoice Forecast'!$A$44

THANK YOU!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2020-05-06T22:50:38+00:00

    First off, your formulas would be a lot easier to read if you only used the worksheet name once when referencing ranges.

    'May Invoice Forecast'!$D$6:$D$44

    is the same as,

    'May Invoice Forecast'!$D$6:'May Invoice Forecast'!$D$44

    So you formula would be,

    =COUNTIFS('May Invoice Forecast'!$D$6:$D$44, "Direct", 'May Invoice Forecast'!$B$6:$B$44, ">=2020-01-01", 'May Invoice Forecast'!$B$6:$B$44, "<=2020-04-10")

    Secondly, your date criteria will break if used on a DYM regional system when your computer uses an MDY regional date system. The same is true for the reverse. Use explicit dates instead of hard coding them into string literals or put the dates into cells and reference the cells.

    =COUNTIFS('May Invoice Forecast'!$D$6:$D$44, "Direct", 'May Invoice Forecast'!$B$6:$B$44, ">="&DATE(2020, 1, 1), 'May Invoice Forecast'!$B$6:$B$44, "<="&DATE(2020, 4, 10))

    Now to get the first name from 'May Invoice Forecast'!$A$6:$A$44 which lies within the date criteria, find the row number with AGGREGATE and pass that to INDEX.

    =INDEX('May Invoice Forecast'!$A:$A, AGGREGATE(15, 7, ROW($6:$44)/(('May Invoice Forecast'!$D$6:$D$44="Direct")*('May Invoice Forecast'!$B$6:$B$44>=DATE(2020, 1, 1))*('May Invoice Forecast'!$B$6:$B$44<=DATE(2020, 4, 10))), ROW(1:1)))

    Drag down for the second, third, etc matches.

    If you want all the names in one cell then use TEXTJOIN to stitch them together in an array formula.

    =TEXTJOIN(", ", TRUE, INDEX('May Invoice Forecast'!$A:$A, AGGREGATE(15, 7, ROW($6:$44)/(('May Invoice Forecast'!$D$6:$D$44="Direct")*('May Invoice Forecast'!$B$6:$B$44>=DATE(2020, 1, 1))*('May Invoice Forecast'!$B$6:$B$44<=DATE(2020, 4, 10))), ROW(1:1))))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-21T22:22:33+00:00

    Hi DishonWH,

    Sorry to bother you again. Regarding to your Excel COUNTIF formula issue for Mac, we are so glad to hear that MVP’s suggestion works for you. Therefore, to help others who may have the similar issue with COUNTIF formula as yours, I would like to add a summary and hope this also benefit them.

    Problem description:

    How to display the value from the rows that qualified using a COUNTIF formula on Mac and would like to display the value (which is a name) that is in the range of cells 'May Invoice Forecast'!$A$6:'May Invoice Forecast'!$A$44 as below:

    =COUNTIFS('May Invoice Forecast'!$D$6:'May Invoice Forecast'!$D$44, "Direct", 'May Invoice Forecast'!$B$6:'May Invoice Forecast'!$B$44, ">=2020-01-01", 'May Invoice Forecast'!$B$6:'May Invoice Forecast'!$B$44, "<=2020-04-10")

    Problem environment:

    Microsoft 365 Apps or Office 365 Business      

    Suggestions:

    @MVP, really thanks for your effort and sharing the suggestion with us, I believe it will also help multiple people. For the guys who encounter the same issue can try what MVP provided:

    1.    Firstly only used the worksheet name once when referencing ranges will be much easier, the formula would be:

    =COUNTIFS('May Invoice Forecast'!$D$6:$D$44, "Direct", 'May Invoice Forecast'!$B$6:$B$44, ">=2020-01-01", 'May Invoice Forecast'!$B$6:$B$44, "<=2020-04-10")2.

    2.    Use explicit dates instead of hard coding them into string literals or put the dates into cells and reference the cells. This step won’t break data criteria with MDY regional data system instead of DYM regional data system.

    3.    Get the first name from 'May Invoice Forecast'!$A$6:$A$44 which lies within the date criteria, find the row number with AGGREGATE and pass that to INDEX.

    =INDEX('May Invoice Forecast'!$A:$A, AGGREGATE(15, 7, ROW($6:$44)/(('May Invoice Forecast'!$D$6:$D$44="Direct")*('May Invoice Forecast'!$B$6:$B$44>=DATE(2020, 1, 1))*('May Invoice Forecast'!$B$6:$B$44<=DATE(2020, 4, 10))), ROW(1:1)))

    4.    Drag down for the second, third, etc matches.

    5.    To apply all the names in one cell, use TEXTJOIN to stitch them together in an array formula.

    =TEXTJOIN(", ", TRUE, INDEX('May Invoice Forecast'!$A:$A, AGGREGATE(15, 7, ROW($6:$44)/(('May Invoice Forecast'!$D$6:$D$44="Direct")*('May Invoice Forecast'!$B$6:$B$44>=DATE(2020, 1, 1))*('May Invoice Forecast'!$B$6:$B$44<=DATE(2020, 4, 10))), ROW(1:1))))

    And we also hope this can benefit others, if you have other issues in the future, please feel free to post a new thread, our engineers will try best to help you.

    Wish you a nice day!

    Please stay safe and take care!

    Regards,

    Gloria

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-05-07T03:28:46+00:00

    THANK YOU!!!!!!!! I appreciate all of your tips. This is incredibly helpful!!!

    Was this answer helpful?

    0 comments