I have 2 columns that I am trying to pull information from:
Row 1 Row2
A: Columbus Detail
B: Columbus Blank
C: Columbus Detail
D: Tulsa Detail
I am looking for a formula that will count how many projects are "Detail and Blank - from Row 2" and are from "Columbus--Row 1". I have been playing around with this for too long and cant figure it out. So I am looking for a formula that will show me "3"
Thanks for you help!
Correcting your syntax to columns, I would suggest something like:
=SUMPRODUCT(--(A2:A5="Columbus"), --($B$2:$B$5="Detail")+($B$2:$B$5="Blank"))
...or based on this layout:A B C D E 1 City Status Find Result 2 Columbus Detail Columbus 3 3 Columbus Blank 4 Columbus Detail 5 Tulsa Detail
=SUMPRODUCT(--(A2:A5=D2), --($B$2:$B$5="Detail")+($B$2:$B$5="Blank"))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
I think you have your rows and columns a bit confused; columns are A, B, C and rows are 1, 2, 3. If your data is organized as shown, they are in Column A and Column B, and rows 1-4.
These formulas will give you what you want:
=COUNTIF(B:B,"Detail")
=COUNTIF(B:B,"Blank")
=COUNTIF(A:A,"Columbus")
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
On seeing JBeaucaire's solution I realize I may have misunderstood what is being asked for; I guess you need a single formula that combines all those conditions. But if everything is either Detail or Blank, why not just count occurrences of Columbus?
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Sorry Guys, I did not mean to break any rules. I was just under a deadline today and needed some help and posted the question on another forum also.
JBeaucaire, I owe you. You solved it! Thanks
That too would be breaking a rule... you need to post links to crossposts...Originally Posted by DaFonz01
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks