Advice on a formula

[datadummy]

I am using the following formula =COUNTIF(Sheet1!F:F,"*MEDICAL CENTER : PROPERTY : BHU") this is returning an incorrect count and I can't figure out why, there are only 9 events and my formula is returning 148. The portion in parenthesis is copied and pasted from the raw data to assure I captured it exactly as it is entered. Any suggestions on a better more precise formula?

 

[awoohaw]

well, i think you need to give some actual data that you don't want counted. Please.

 

[datadummy]

I type before I think sometimes....

Did you type MEDICAL CENTER : PROPERTY : BHU in cell H200?

Oh I think I did that process wrong I put MEDICAL CENTER : PROPERTY : BHU in cell J6 "criteria" and referenced J:J.

 

[Skyybot]

No the formula is on Sheet2.

That's fine. Type it into H200 on Sheet2.

 

[datadummy]

For what its worth I copied and pasted the filtered data to a separate tab and then tried the formula and it returned 9 as expected.

 

[awoohaw]

I'm happy you figured it out!

 

[datadummy]

That's fine. Type it into H200 on Sheet2.

Ok here is what I have entered =COUNTIF(Sheet2!F:F,Sheet2!H200) it returned 0. Should I take the Sheet2 reference out as I have put the formula on Sheet3?

 

[Skyybot]

For what its worth I copied and pasted the filtered data to a separate tab and then tried the formula and it returned 9 as expected.

There must've been some hidden rows.? Oh well, it's working, right?

 

[Skyybot]

Ok here is what I have entered =COUNTIF(Sheet2!F:F,Sheet2!H200) it returned 0. Should I take the Sheet2 reference out as I have put the formula on Sheet3?

yes

 

[Skyybot]

yes

Now type criteria in H200 Sheet3

 

[datadummy]

There must've been some hidden rows.? Oh well, it's working, right?

If I filter and then copy and paste it works, I'm hoping to avoid the copy and paste and just be able to reference the unfiltered data.