Countif function for multiple criteria

0
votes

I am trying to create a formula in Excel that will count cells with 3 certain criteria. My example is below: cell M3:M350 is the name, cell Q3:Q350 is the date, cell R3:R350 is the current step. I need to know how many times the name Amanda shows up that is in the current step of Material Review, and that is also 30 days or less than the date in cell Q3:Q350. The formula I have been trying is: 

=COUNTIFS('Active Projects'!M3:M350,"AMANDA MARTIN",'Active  
Projects'!Q3:Q350,"<="&NOW+30,'Active Projects'!R3:R350,"*MATERIAL REVIEW")

The answer keeps giving me 0, and it should be 1.

Does anybody know the correct formula to use? It has been eating at me for a week now, and I have tried different variations, and still cannot get it to not count the dates older than 30 days from today.

1
excel-formulacountif
Can you add some example data so we can better understand the situation? – Rob

1 Answers

0
votes

please update your formula as follows:

=COUNTIFS('Active Projects'!M3:M350,"AMANDA MARTIN",'Active Projects'!Q3:Q350,"<=" & TODAY() + 30,'Active Projects'!R3:R350,"*MATERIAL REVIEW")

Structure you have used for date is not correct:

=COUNTIFS('Active Projects'!M3:M350,"AMANDA MARTIN",**'Active Projects'!Q3:Q350,"<="&NOW+30**,'Active Projects'!R3:R350,"*MATERIAL REVIEW")

there should be brackets () after NOW or TODAY functions.