Help with Countif formula

[YOUNAN]

Hello,

I am using this formula to count how may times 2014 is repeated in a range

=countif(u4:u13;"2014").

I need to count how many times "2014" is repeated though multiple sheets , the range is same.

Thanks in advance.

 

[n1lesh]

=COUNTIF(U4:U13,2014)
Dont need quotes around the 2014

 

[YOUNAN]

Thanks for your reply, when removing quotes I get 0, I have 2014 repeated 3 times, but when I put back the quotes I get 3 . any help on a formula that can do this but count through multiple sheets.

Thanks in advance

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$5&"'!U4:U13"),2014))

where A2:A5 houses the relevant sheet names (adjust to suit).

 

[kvsrinivasamurthy]

This ARRAY formula works.
Z1:Z3 in the same sheet contains the sheet names. Eg Z1=Sheet1,Z2=Sheet2 , etc

=SUM(COUNTIF(INDIRECT("'"&$Z$1:$Z$3&"'!U4:U13"),2014))

How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.