Using a COUNTIF formula on one column if a specific phrase exists in another column.

[thingstodo123]

Hi all,

This has completely stumped me unfortunately. I'm trying to report on a data sheet using Excel and can't seem to make the formulas work.

I have attached a screenshot to add context. Excel Issue.JPG

So in short, if Column B has a 10bn in it, I would like to report on the numbers in Column H for that row.

For example, Column B10 has a 10bn in it, so then I want to run a formula that says, if the number in Column H is over 10 then it counts as one. If it's 9 and below, it's a zero. Then I can add up how many companies have 10+ contacts associated with them.

I could separate the sheet into only 10bn+ but and then report on that directly but I'm hoping someone can help me avoid that!

Any help would be greatly appreciated.

Many thanks,

Michael

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[thingstodo123]

Ah nice, sorry been a while since I've been on, shall get one up now.

[canapone]

Hi

maybe you could try

=COUNTIFS(B$3:B$26,"10bn",H$3:H$26,">=10")

Regards

[thingstodo123]

Thanks a lot, that's a great help.

I've tried a workaround of using a vlookup to decide on tiers, then this =IF(H11>=$AB$5,$AB$7,IF(H11>=$AA$5,$AA$7,IF(H11>=$Z$5,$Z$7,""))) to sort out whether a row is complete, partial or incomplete and then filled in the table with this =COUNTIFS($I$3:$I$187,$Y8,$J$3:$J$187,Z$7)

Although, I think your way may be simpler.

Thanks,

Michael