LinkBack URL
About LinkBacks
Good Afternoon,
I have a table
Dept No Category
10 Old
12 Old
10 New
15 Old
18 New
10 New
15 Old
12 New
I want to achieve this result
Dept New Old
10 2 1
12 1 1
15 0 2
18 1 0
Im not sure how to do this --Your help is greatly appreciated.
Good Afternoon,
I have a table
Dept No Category
10 Old
12 Old
10 New
15 Old
18 New
10 New
15 Old
12 New
I want to achieve this result
Dept New Old
10 2 1
12 1 1
15 0 2
18 1 0
Im not sure how to do this --Your help is greatly appreciated.
A pivot table with Dept as Rows, Category as columns and count of Category as Sums will do you.
Last edited by MarvinP; 09-28-2010 at 03:38 PM.
Hmm....I did not get it....Not sure how its done
Sorry that this site is nt that helpful. This is the 3rd time that I did not get the right answer.
=COUNTIFS(A2:A9,10, B2:B9, "Old")
Thats the formula
Thanks
Hi raginyi,
I'm sorry I didn't come up with your final answer. I was thinking you had a lot of data to deal with.
If you want to test our ability in the future, please let us know it's a test and you already have the answer. That way we can all guess what you're thinking, or perhaps just work with people who want our help.
In all seriousness, it is much easier to work with an existing workbook with test data. I guess a lot of people have never clicked on the "Go Advanced" button and uploaded a test file. Needing to create your data puts us at a disadvantage as it may not be what you have or need.
I'm always ready to learn and have recreated my pivot table and used your formula in the attached. I like my answer better. I even have a GrandTotal for each Dept No. Your formula only gives a single number. Is that what you wanted? I guess not
.
This forum is GREATLY helpful to me!
Thank you 4am, lots of people share that view
raginiy,
MarvinP supplied you with a way to solve your problem. Often there are several ways to accomplish something in Excel. Here, for instance I'm sure you could use a pivot table, formulas or VBA.
Marvin's suggestion would be preferable to many as it automatically supplies the results without you having to provide the criteria, as you would with formulas.
I suggest that you would improve your experiences here if you do the following:
1.) Provide as much relevant information up front as possible, including your preference for methods if you have any
2.) Thank anybody who provides you with a solution, even if that solution isn't entirely suitable for you.
3.) If a suggested solution isn't suitable for you then perhaps explain why and give any other relevant information.
Have a nice day
Audere est facere
raginyi,
Your profile says you are on Excel 2003, COUNTIFS are for 2007 up. If you want it compatable with 2003, you'll need a different equation. Assuming your headers are on sheet2 in row 1 and col A, in B2
=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(Sheet1!$B$2:$B$9= B$1))
Last edited by ChemistB; 09-28-2010 at 04:30 PM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
raginiy
You don't have to post here for your free help! There's lots of Excel consultants that you can pay
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Marvin's solution is (recognising my state of enormous ignorance) far better to me.
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks