Hi All,
Am new to macros, i have excel file which has 5 columns in a sheet & i need the following operations to perform.
1. Need to count the column C -- for this need to have one button, click on this button it should give output in message box
2. There is column called Priority with values (P1/P2/P3/P4) -- for this need to have one button, click on this, it should give output as
P1 =4
P2=1
P3=1
P4=0
3. There is another column by name Status with values (closed/open/progress) -- need to have button, click on this it should give output as
Closed=4
Open=1
Inprogress= 2
4. There is another column by name Type with values (CR/Defects) -- need to have an output says
CR =2
Defects=5
Input file will look like this :
ID Defects ID Priority Status Type
1 3434 P1 open defect
2 3452 P1 closed defect
3 3553 P1 inprogress defect
4 4535 P1 open CR
5 5454 P2 closed defect
6 4646 P3 open CR
Anyhelp please
thanks,
Viv
You don't need macros or buttons, just use COUNTIF and/or COUNTIFS.
Regards, TMS
As TM suggests, you could do this with formulas, but... if your really, really want to go way of the macro, please post a dummy workbook with a before and after outcome, it will make it easier to get an idea on what you are after.
If you are happy with the answer, please click the Star icon in the below left hand corner.
Good sites to start learning.
snb's VBA Help Files
Jerry Beaucaires Excel Assistant
J & R Excel Consultancy Services
How to post code correctly: Correct Code Posting
Why would anyone want to have to press a whole series of buttons to get message boxes with numbers when they can have a constant running total on a summary sheet.
As you say though, a sample workbook would help in developing whatever solution.
Regards, TMS
Last edited by TMShucks; 01-26-2012 at 04:45 AM.
I agree completely TM. But, I can smell a little bit of "we are not getting the whole story" happening here.
If you are happy with the answer, please click the Star icon in the below left hand corner.
Good sites to start learning.
snb's VBA Help Files
Jerry Beaucaires Excel Assistant
J & R Excel Consultancy Services
How to post code correctly: Correct Code Posting
Thanks for the reply for all.
I have attached the input file format & as well the expected output (which is there in input file itself)
May be everyone said, some formula will do i guess but i don't know how to do that..
Thanks,
Viv
Hi Viv
I would recommend setting up a summary sheet as shown in the updated example.
You can then use the following formulae to get the numbers you want.
There's a version of the formulae that uses Named Ranges; for that, you must NOT have anything in column A other then the IDs.
HTML Code:Using Named Ranges 10 =COUNTA(ID) =COUNTA(owssvr!$A$2:$A$11) 4 =COUNTIF(Priority,"P"&ROW(A1)) =COUNTIF(owssvr!$D$2:$D$11,"P"&ROW(A1)) 4 =COUNTIF(Priority,"P"&ROW(A2)) =COUNTIF(owssvr!$D$2:$D$11,"P"&ROW(A2)) 1 =COUNTIF(Priority,"P"&ROW(A3)) =COUNTIF(owssvr!$D$2:$D$11,"P"&ROW(A3)) 1 =COUNTIF(Priority,"P"&ROW(A4)) =COUNTIF(owssvr!$D$2:$D$11,"P"&ROW(A4)) 1 =COUNTIFS(Type,"Defect",Status,"open") =COUNTIFS(owssvr!$E$2:$E$11,"Defect",owssvr!$F$2:$F$11,"open") 1 : =COUNTIFS(owssvr!$E$2:$E$11,"Defect",owssvr!$F$2:$F$11,"closed") 5 : =COUNTIF(owssvr!$F$2:$F$11,"Resolved") 2 and so on =COUNTIF(owssvr!$F$2:$F$11,"inprogress") 2 : =COUNTIFS(owssvr!$D$2:$D$11,"P"&ROW(A1), owssvr!$E$2:$E$11,"CR") 1 : =COUNTIFS(owssvr!$D$2:$D$11,"P"&ROW(A2), owssvr!$E$2:$E$11,"CR") 3 =COUNTIFS(owssvr!$D$2:$D$11,"P2", owssvr!$E$2:$E$11,"Defect") 2 =COUNTIFS(owssvr!$D$2:$D$11,"P1", owssvr!$E$2:$E$11,"Defect") 1 =COUNTIFS(owssvr!$D$2:$D$11,"P4", owssvr!$E$2:$E$11,"Enhancement") 1 =COUNTIFS(Priority,"P3", Type,"Enhancement") =COUNTIFS(owssvr!$D$2:$D$11,"P3", owssvr!$E$2:$E$11,"Enhancement")
Thanks TMShucks. your response gives an idea on how to go about with formula's..
Thanks Again & to all.
Regards,
Viv_81
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks