• 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.

Adding the values of columns if the Category in one cloumn has valu of pass, fai

P

Prabha

New Member
Adding the values of columns if the Category in one cloumn has value of pass, fail and No run in other column, then want sum of Pass, fail and No Run in seprate columns


Category Test Cases Pass/Fail

Account TC001 Pass

Account TC002 Fail


F01

F03

.

.

.

.so on
 
Check out the SUMIF or COUNTIF function. Based on your example, I think you want something like:

=COUNTIF(C:C,"Fail")
 
If I read the post correctly there were two questions posed. One being finding the sum of values based on a pass/fail/no run text string. Second question was to count the # of Pass/fail/no run.


Luke answered the second question. The first can be found by using sumproduct.
 
Thanks Luke,


Montery Correct, i want find with cateogrey and add them in one column like pass , fail, and no run
 
Column A: Categorey

Column B : Test Cases id

Column C: Status

Column D: pass

Column E: Fail


I want to search for Account cateogrey from Column A and add all pass for this categorey and put the value in Pass column of D similalry Fail and No run


hoe this is clear


Category/User TestCAse ID Status (Pass/Fail) Pass Fail No Run

Account TC001 Fail

Account TC002 No Run

Rules TC001 Pass

Rules TC002 Fail

Rules TC003 No Run
 
Check out the 2nd stickey at the top of the forums for how to upload a spreadsheet. I'm afraid I'm still having trouble understanding what it is you want exactly.
 
Hi Prabha,


Please select K2, and paste the below formula.

Code: 
=COUNTIF($F$2:$F2,K$1)

Now copy K2, and paste in all the area of K,L,and M (upto how many row you required)

[pre]Pass	                Fail	                No Run
=COUNTIF($F$2:$F2,K$1)	=COUNTIF($F$2:$F2,L$1)	=COUNTIF($F$2:$F2,M$1)
[/pre]

Please let us know the result.


Regards,

Deb
 
Hi, Prabha!

Type this in K2 and copy across thru N2:

=CONTAR.SI($F:$F;K$1) -----> in english: =COUNTIF($F:$F,K$1)

Delete formulas for K3:K6 cell range too.

Regards!


EDIT: Oops! Sorry, Debraj Roy, didn't see your message. Perhaps some day I'll learn to refresh (F5) screen before posting.
 
@ Debraj,


Thanks for response, i might posted question wrongly here is what iam looking for?


I want find the category = Account (COlumn B) and want to see how many are total failed, passed and norun in each respcted columns of pass, fail & No run


For example: Categorey- Account - 2 pass, 2 fail 2 No RUn like that, hope this is clear...


Thnx
 
Hi, Prabha!

The same formula is used in K2:N2 and checks column $F:$F for values in first row of each column (K1, L1, ...). So if you keep the words checking for (Pass, Fail, No Run, NA) in range K1:N1 it should work.

Regards!
 
@ SirJB7,


Iam looking for each categorey how many pass fail and no run?


Example:


Cloumn B - Cateogrey


Iwant how many passed , failed , Not run for this particular categorey and similalry other categoreis...
 
Column A: Categorey

Column B : Test Cases id

Column C: Status

Column D: pass

Column E: Fail


I want to search for Account cateogrey from Column B and add all pass for this categorey and put the value in Pass column of D similalry Fail and No run
 
Hi Prabha,


In K2,change the formula to.

Code: 
=COUNTIFS($C$2:$C2,$C2,$F$2:$F2,K$1)


By the way, do you have Excel 2007 or any prior edition..


Regards,

Fan of SirJB7..


EDIT: you're gonna make me blush :$
 
Hi, Prabha!


I'm now reading your last comments and I think there are differences between your column description of http://chandoo.org/forums/topic/adding-the-values-of-columns-if-the-category-in-one-cloumn-has-valu-of-pass-fai#post-37524 and your actual uploaded file of http://chandoo.org/forums/topic/adding-the-values-of-columns-if-the-category-in-one-cloumn-has-valu-of-pass-fai#post-37508.


The difference seems to be two columns: A, Release/Regression, and B, Priority.

What you've asked about counting is solved yet with Debraj Roy's suggestion http://chandoo.org/forums/topic/adding-the-values-of-columns-if-the-category-in-one-cloumn-has-valu-of-pass-fai#post-37511 or with mine http://chandoo.org/forums/topic/adding-the-values-of-columns-if-the-category-in-one-cloumn-has-valu-of-pass-fai#post-37514.


Check it downloading this file:

https://dl.dropbox.com/u/60558749/Adding%20the%20values%20of%20columns%20if%20the%20Category%20in%20one%20cloumn%20has%20valu%20of%20pass%2C%20fai%20-%20TCM%20Execution%20status%20%28for%20Prabha%20at%20chandoo.org%29.xlsm


It's a 2010 Excel version file, updated today to SP1, so I'll discard any incompatibility issue.


If you have further requirements, please elaborate a bit more on them and upload, if necessary, a new sample file including one of our solutions and your new output required, written manually and explained in detail.


Regards!
 
You must log in or register to reply here.
Back
Top