Counta or Countif for 1 range but multiple criteria

[DCexcels]

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

[#2]

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.

[#3]

Hi @Faraz Shaikh
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.

[#4 · Accepted solution]

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.

[#5]

Hi @Faraz Shaikh
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