search a range for text value

[tink bell]

hi!

I'm pulling my hair out trying to figure out the best formula to use for this task. I've goolged & came up with this formula:

=IF(COUNTA(J2:J8="Yes"),"Yes","")

when J2, et al, contain the formula:

=IF(E5=$J$1,"Yes","")

however, when I have a range that does not return any "yes" values, my counta formula still returns a "yes". what am I doing wrong? is this not the best formula for this task?

in addition, this is a sub-totaled spreadsheet as opposed to a pivot (just seemed to work better w/all my formatting needs). what's the best way to CORRECTLY copy down the formulas on the sub-total line other than Go To Special > Visible Only? the formulas aren't lining up correctly (overlapping w/above formula/ranges).

THANKS so much!!!

 

[Fishboy]

hi!

I'm pulling my hair out trying to figure out the best formula to use for this task. I've goolged & came up with this formula:

=IF(COUNTA(J2:J8="Yes"),"Yes","")

when J2, et al, contain the formula:

=IF(E5=$J$1,"Yes","")

however, when I have a range that does not return any "yes" values, my counta formula still returns a "yes". what am I doing wrong? is this not the best formula for this task?

Hi Tink Bell, welcome to the boards.

Why not just =COUNTIF(J2:J8,"Yes") for a numerical count or

=IF(J2:J8="Yes","Yes","") entered with CTRL+Shift+Enter for a Yes or blank answer.

in addition, this is a sub-totaled spreadsheet as opposed to a pivot (just seemed to work better w/all my formatting needs). what's the best way to CORRECTLY copy down the formulas on the sub-total line other than Go To Special > Visible Only? the formulas aren't lining up correctly (overlapping w/above formula/ranges).

THANKS so much!!!

I'm not 100% sure I follow. Are you able to post an example of your data? If it makes life easier google MrExcel HTML Maker and you can simply copy / paste an example on the forums directly.

 

[tink bell]

Hi Tink Bell, welcome to the boards.

Why not just =COUNTIF(J2:J8,"Yes") for a numerical count or

=IF(J2:J8="Yes","Yes","") entered with CTRL+Shift+Enter for a Yes or blank answer.


I'm not 100% sure I follow. Are you able to post an example of your data? If it makes life easier google MrExcel HTML Maker and you can simply copy / paste an example on the forums directly.

yes, that worked, thank you.


unfortunately, I cannot post an exact example. I've manually corrected the problem already (~900 rows!). I'd just like to know how to correct the problem for future reference as this is a report I'm going to have to replicate daily for a few weeks. the problem was while the formula was being pasted into the visible cells as it should, the range above each formula would overlap into the previous (above) formula. for example:

j800 contains the formula currently: =IF(COUNTIF(J792:J799,"Yes"),"Yes","")

however, previously it may have read: =IF(COUNTIF(J789:J799,"Yes"),"Yes","")

as you can see J789 would have carried into the previous range or group shown here:

j791 contains: =IF(COUNTIF(J789:J790,"Yes"),"Yes","")

I hope this is more clear, although still mucky. I had to manually go to each subtotal line where I had all my formulas & manually expand or decrease the range as needed.

thanks again. your input is much appreciated!

 

[Fishboy]

I'm glad the first part worked at least, but unfortunately without context the latter part about ranges is very confusing. I suspect even posting sample data that has been corrected already would at least give us a visual idea of what the problem is and how it can be resolved.

 

[tink bell]

ok, i'll post it Monday when I re-run the report. how exactly would you like to see the issue? screen shot w/the formula visible? fyi - I tried to d/l the html maker but am getting any error about 64 bit something or other.

thanks & have a great weekend!

 

[tink bell]

I'm glad the first part worked at least, but unfortunately without context the latter part about ranges is very confusing. I suspect even posting sample data that has been corrected already would at least give us a visual idea of what the problem is and how it can be resolved.

hi, I have a couple snippets of how the formulas aren't selecting the proper subtotaled ranges when I copy/paste visible, but I can't figure out how to upload a screen shot & I couldn't figure out how to install the html maker. I got an error - something about 64 bit something or other.

I thought about subtotaling this column as well & then somehow replacing the formula w/the correct formula to maintain the integrity of the subtotal ranges, but I can't for the life of me figure out how to replace a formula this way, keeping the respective ranges. I've already been working on this for the last 2 hrs. I realize it's an investment of time & once solved, it will be much faster than manually adjusting the range for each lines' formula as i'll be re-creating this report daily for the next 3 weeks & again quarterly.

sorry, while pretty excel savvy compared to *most* in my office, compared to you guys I'm a total noob. I sincerely appreciate your help!!!