Hi all - is anybody able to help me with what is probably a simple fix....
i am looking for a formula to.....
if a cell in column A10:A100="YP45869" count how many times "RBO Shirts" appears in column B10:B100
Many thanks in advance
Hi all - is anybody able to help me with what is probably a simple fix....
i am looking for a formula to.....
if a cell in column A10:A100="YP45869" count how many times "RBO Shirts" appears in column B10:B100
Many thanks in advance
Last edited by Ash Ford; 04-11-2013 at 12:04 PM. Reason: Sloved
=SUMPRODUCT(--($A$2:$A$11="YP45869"),--($B$2:$B$11="RBO Shirts"))
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMS - many thanks
i still cannot get a result - the result cell which should equal 1 remains blank?
YP45869 RBF 97
YM56789 QPF 56
YP45869 RBO Shirts
YP45869 RBF 108
any ideas ??
Ash,
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Special-K,
Sorry about it, but i guess you didnt see post 4.
Thank you for complying with the forum rules and changing your thread title. Interestingly, your new title is in some ways less descriptive than the old one in that you specifically want a formula for Excel 2003.
If you had a later version of Excel, you could use COUNTIFS rather than SUMPRODUCT.
That said, Special-K is right ... your data is not as it seems. It must have spaces or non printing characters that prevent the match. The formula could be modified to use LEFT and/or MID but it would be better to understand your data.
I suggest that you post a sample workbook for us to assess.
Regards, TMS
Hi Special K
showing my true colours now - i cannot seem to attach the spreadsheet!!
Last edited by Ash Ford; 04-11-2013 at 07:35 AM.
Hi all please now find attached the spreadsheet
C2: =SUMPRODUCT(--($B$7:$B$12=$A2),--($C$7:$C$12=C$1))
You have trailing spaces on the lists in column A and column B. The above formula will work if you sort out your data.
Regards, TMS
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks