Search code examples
excel-formulaexcel-2007

how to using countifs ( excel 2007 ) to return data that meet "text" criteria on specific month?

im beginner with microsoft excel , my case is to return set of data on the table using ifcounts, the objective is return all data that meet "text" criteria on specific month.

enter image description here

let say , i want to show all data that meeet criteria 1 ( 1. New ) and criteria 2 is month (06 / june ). i have tried with

=COUNTIFS('FIRST SHEET'!D2:D35,"1. New",RESULT SHEET!A2:L31,MONTH('FIRST SHEET'!C2:C30)=06)

i got no result at all

is any better way to do this case

thanks

Solution

  • Countifs logic works like (range,condition), not (array=condition), like you demonstrated at: MONTH('FIRST SHEET'!C2:C30)=06).

    SUMPRODUCT however can handle arrays:

    =SUMPRODUCT(("1. New"='FIRST SHEET'!D2:D32)*(ISNUMBER(SEARCH("*"&"/06/"&"*",'FIRST SHEET'!C2:C32))))

    Edit: if the date column are actual date values than you need to count the instances greater than the first of the month and smaller than the first of the next month (rather than equal to or smaller than the last date of the month, because if the date column also has time value it would be recognized as greater than that day by Excel):

    =COUNTIFS('FIRST SHEET'!D2:D35,"1. New",'FIRST SHEET'!C2:C30,">="&DATE(2022,6,1),'FIRST SHEET'!C2:C30,"<"&DATE(2022,7,1))