Forum Discussion

DCexcels's avatar
DCexcels
Copper Contributor
May 16, 2021
Solved

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...
BI & Data Analysis
excel
Formulas and Functions
office 365
  • ExcelExciting's avatar
    ExcelExciting
    May 17, 2021

    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.

DCexcels's avatar
DCexcels
Copper Contributor
May 16, 2021
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.
ExcelExciting's avatar
ExcelExciting
MVP
May 17, 2021

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.

  • DCexcels's avatar
    DCexcels
    Copper Contributor
    May 17, 2021
    Hi ExcelExciting
    Thank you for the solution. This was very helpful! I'm still new to excel functions but having a lot of fun learning the syntax.
    Stay safe,
    Aaron

Resources