Forum Discussion

DCexcels's avatar
DCexcels
Copper Contributor
May 16, 2021

Counta or Countif for 1 range but multiple criteria

Hi there,

Apologies if this has already been asked but I can't seem to find an answer. I have a MS Forms that Power Automates specific answers from various questions into a a table within a workbook. Some, not all, questions are populated into the workbook table, where each question that is extracted into the workbook, is represented by a column.

I am trying to use COUNTA or COUNTIF in a different worksheet within the same workbook. I am attempting to use COUNTIF/COUNTA for the range of column D of sheet one in reference to the date of the form submission. Column D has entries in cells that start with brackets, eg. ["1", "2"]. For instance, in table 1 - worksheet 1, the same date can appear multiple times for various rows in sequence for different submissions. In worksheet 2 - column B (not a table), there can only be one date for each row (also in ascending sequence). COUNTIF usually works, but for some reason it's not working for this situation. I thought maybe COUNTA could work if paired with a IF function of sorts, but so far I can't find a functional solution. Any suggestions please?

For clarity: Range = Column D, Criteria 1 = non-blank cells of column D, Criteria 2 = Date

 

Thank you,

Edit: Added the for clarity sentence

  • Hi DCexcels 

     

    The formula should read like this a below, where you need to use COUNTIFS with multiple criterias 

    1. Count the values between the date range
    2. Count value which are not equals to blank
    3. Count Based on your product
    =COUNTIFS(Table1[Date Reformatted],">="&$B2,Table1[Date Reformatted],"<="&$B2,Table1[Which tomatoe edition?],"<>",Table1[What does the client want],"tomatoes")

     

    So I have added additional column when you have product reads as "Tomatoes and Apples", if you don't like that then you just add those values using the same logics of countifs that is showed in sheet3

     

     

    Attached is the sample file.

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

  • Hi DCexcels 

     

    Could you please attach a sample dataset to advise you correct solution 

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

    • DCexcels's avatar
      DCexcels
      Copper Contributor
      Hi ExcelExciting
      I will attach a sample dataset that mimics some parts of my problem. In this case, column K is the range that I'm trying to count without success with respect to the given date.
      • Hi DCexcels 

         

        The formula should read like this a below, where you need to use COUNTIFS with multiple criterias 

        1. Count the values between the date range
        2. Count value which are not equals to blank
        3. Count Based on your product
        =COUNTIFS(Table1[Date Reformatted],">="&$B2,Table1[Date Reformatted],"<="&$B2,Table1[Which tomatoe edition?],"<>",Table1[What does the client want],"tomatoes")

         

        So I have added additional column when you have product reads as "Tomatoes and Apples", if you don't like that then you just add those values using the same logics of countifs that is showed in sheet3

         

         

        Attached is the sample file.

         

        Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

        If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

Resources