# Countifs

Hi guys,

Please help me in countifs formula to count *text* in a column with  current date and one more criteria is with specfic row name. I was trying to write countifs formula with index match , here i was applying double match but i cant. Here sharing sheet example with you guys please help.

When i use simply countifs formula it works but want to make this formula independent of column name by using index match

13 Replies

# Re: Countifs

In K2:

=COUNTIFS(Chhattisgarh!\$H\$3:\$H\$1000, \$A2, INDEX(Chhattisgarh!\$Q\$3:\$ZZ\$1000, 0, MATCH(K\$1, Chhattisgarh!\$Q\$2:\$ZZ\$2, 0)), "*")

# Re: Countifs

Thank you its working, can i not apply index match too for chhattisharh!H3:H1000 column to get match A2

# Re: Countifs

One more query : can yoy please convert this formula also to with index match and with two different text count in a different way

I am using this currently : =COUNTIFS(Chhattisgarh!Q:Q,"*cnp*",Chhattisgarh!\$H:\$H,\$A2)+ COUNTIFS(Chhattisgarh!Q:Q,"*cnc*",Chhattisgarh!\$H:\$H,\$A2)

# Re: Countifs

In K2:

=SUM(COUNTIFS(Chhattisgarh!\$H\$3:\$H\$1000, \$A2, INDEX(Chhattisgarh!\$Q\$3:\$ZZ\$1000, 0, MATCH(K\$1, Chhattisgarh!\$Q\$2:\$ZZ\$2, 0)), {"*cnp*", "*cnc*"}))

# Re: Countifs

Thank you so much!! Suggest me how can i clear my formula concept like u... It will be honour for me to get a teacher or mentor like you

# Re: Countifs

I usually visit Exceljet when I want to learn about functions and formulas.

# Re: Countifs

@Hans Vogelaar Hi Hans, i used this formula as you put here, but the result is only doing sum for "cnp" , not for "cnc"

# Re: Countifs

Does it help if you confirm the formula by pressing Ctrl+Shift+Enter?

# Re: Countifs

Even if the tag of your post is "Excel for web", you use Google Sheets. They are working differently. Some functions not compatible, some give the same result, some give different results.

# Re: Countifs

Thank you so much , its working with cntrl + shift+ enter.

And i applied the same formula to count and sum two crietria "okay" and "Try" then its not working. Sharing screenshot with you.

# Re: Countifs

I'd have to see a copy of the workbook (without sensitive data).

# Re: Countifs

Hi, actually i want to use this formula in a simpler way

=COUNTIFS(Chhattisgarh!N:N,"Okay",Chhattisgarh!I:I,B3)+COUNTIFS(Chhattisgarh!N:N,"Try",Chhattisgarh!I:I,B3)

# Re: Countifs

Hey Hans i got the solution,

=ArrayFormula(SUM(COUNTIFS(INDIRECT(\$A3&"!I:I"),B3,INDIRECT(\$A3&"!N:N"),{"*Okay*","*Try*"})))

This is working