Countifs

Copper Contributor

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

@Rfire777 

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)), "*")

Thank you its working, can i not apply index match too for chhattisharh!H3:H1000 column to get match A2
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)

@Rfire777 

 

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*"}))

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

@Rfire777 

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

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

@Rfire777 

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

@Rfire777 

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.

@Hans Vogelaar 

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.

@Rfire777 

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

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)
Hey Hans i got the solution,

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

This is working