• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula = text string in one colomun but not = another text in different column?

B

bmoher

New Member
Hi,

This sounds simple but may be deceptively tricky to work out. I have been having problems working out a formula for this in XL 2007. I'm trying to count the occurences of a string of text(INTEL)in one column and a sting of text (DECOMM.) not occur in another column. So, positive on one string of text occurring in one column and negative on the other string of text occurring in another column.


So far the only workaround I have come up with is to test for every other possible string(Prod, Dev, QA, etc.) except the one I don't want to count (DECOMM.) and add them up but there are a lot of different strings and this is quite awkward.


So far I have tried count, counta, dcount, countif, sum, and several others. I Have been trying pivot tables today but that also seems to be a dead end.


Any help would be greatly appreciated as this seems quite tricky and even the resident XL guru has been humbled by this.
 
I'm not sure I'm understanding. So:


a | b

intel | decom


You want to count rows where a = intel and b /= decom?


I'm really not good with this stuff. But my first duct tape work around would be a third column with: =concatenate(a1,b1), and then you can count the occurrences of 'intel' in A less the count of inteldecom in column c. If I'm understanding the problem right, that should get it done.


Again, it's not elegant and it's not pretty. But it'll probably work.
 
BMOHER

Try this

=+SUMPRODUCT((A1:A10="intel")*(B1:B10<>"decom"))


adjust to suit
 
Thanks Hui, I tried it and it Works! It really helps clean up what would otherwise be a sloppy way of doing it.
 
You must log in or register to reply here.
Back
Top