SOLVED
Home

Excel Formula HELP

%3CLINGO-SUB%20id%3D%22lingo-sub-482299%22%20slang%3D%22en-US%22%3EExcel%20Formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482299%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BI%20need%20to%20take%20the%20%3CSTRONG%3Esum%3C%2FSTRONG%3E%20of%20the%20cells%20in%20column%20%22B%22%20(that%20have%20the%20text%20%22R%22)%20and%20column%20%22C%22%20(that%20have%20the%20text%20%22Acc%22).%20Also%20I%20need%20to%20count%20all%20cells%20in%20column%20%22C%22%20that%20have%20the%20text%20%22Rej%22.%26nbsp%3B%20But%20I%20need%20a%20formula%20that%20will%20allow%20me%20to%20take%20the%20total%20cell%20count%20I%20get%20from%20column%20C%20with%20%22Rej%22%20and%20%3CSTRONG%3Esubtract%3C%2FSTRONG%3E%20from%20that%2C%20the%20total%20I%20get%20from%20the%20sum%20of%20the%20cells%20of%20columns%20B%20and%20C%20(that%20meet%20the%20above%20criteria).%3C%2FP%3E%3CP%3EI'm%20sure%20there%20is%20an%20easier%20way%20to%20explain%20that%2C%20so%20I%20apologize%20if%20it%20sounds%20confusing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-482299%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482586%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302344%22%20target%3D%22_blank%22%3E%40Celia_Alves%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECelia%2C%20that%20was%20almost%20perfect.%20It%20was%20just%20backwards%2C%20so%20I%20switched%20and%20it%20worked%20perfectly.%3C%2FP%3E%3CP%3E%3DCOUNTIFS(C6%3AC1836%2C%22Rej%22)-COUNTIFS(B6%3AB1836%2C%22R%22%2CC6%3AC1836%2C%22Acc%22)%3C%2FP%3E%3CP%3ENot%20sure%20why%20I%20could%20not%20figure%20that%20out%2C%20but%20it%20was%20killing%20my%20brain..lol.%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20your%20help.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-482429%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-482429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326683%22%20target%3D%22_blank%22%3E%40WillJa67%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20there!%20I%20am%20not%20sure%20if%20I%20get%20your%20explanation.%3C%2FP%3E%3CP%3EI%20assumed%20that%20when%20you%20say%20%22sum%20of%20the%20cells%22%20you%20meant%20%22count%20of%20rows%22%20where%20the%20criteria%20were%20met.%3C%2FP%3E%3CP%3EIf%20that's%20the%20case%2C%20please%20try%20the%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIFS(B%3AB%2C%22R%22%2CC%3AC%2C%22Acc%22)-COUNTIFS(C%3AC%2C%22Rej%22)%3C%2FP%3E%3CP%3EIf%20that's%20not%20the%20case%2C%20please%20let%20me%20know.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20file%20attached%20with%20the%20formulas%20and%20an%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-486505%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-486505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F326683%22%20target%3D%22_blank%22%3E%40WillJa67%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20My%20pleasure%20to%20help.%3C%2FP%3E%3CP%3EIt%20has%20also%20happened%20to%20me%20before%3A%20spent%20hours%20of%20work%20and%20wasted%20hours%20of%20sleep%20trying%20to%20find%20a%20solution%3B%20then%20decided%20to%20ask%20for%20help%20in%20a%20forum.%20When%20I%20saw%20the%20answer%2C%20I%20could%20not%20believe%20I%20had%20not%20been%20able%20to%20figure%20that%20out%20myself.%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20can%20mark%20my%20answer%20as%20the%20best%20answer%2C%20it%20would%20be%20great!%20Enjoy%20your%20weekend%20and%20have%20lots%20of%20fun%20with%20Excel!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
WillJa67
New Contributor

 I need to take the sum of the cells in column "B" (that have the text "R") and column "C" (that have the text "Acc"). Also I need to count all cells in column "C" that have the text "Rej".  But I need a formula that will allow me to take the total cell count I get from column C with "Rej" and subtract from that, the total I get from the sum of the cells of columns B and C (that meet the above criteria).

I'm sure there is an easier way to explain that, so I apologize if it sounds confusing.

 

3 Replies
Highlighted

@WillJa67 

Hi, there! I am not sure if I get your explanation.

I assumed that when you say "sum of the cells" you meant "count of rows" where the criteria were met.

If that's the case, please try the formula 

=COUNTIFS(B:B,"R",C:C,"Acc")-COUNTIFS(C:C,"Rej")

If that's not the case, please let me know.

 

Please see the file attached with the formulas and an example.

Highlighted

@Celia_Alves 

Celia, that was almost perfect. It was just backwards, so I switched and it worked perfectly.

=COUNTIFS(C6:C1836,"Rej")-COUNTIFS(B6:B1836,"R",C6:C1836,"Acc")

Not sure why I could not figure that out, but it was killing my brain..lol.

Thank you so much for your help.


 


 

Highlighted
Solution

@WillJa67 

You're welcome! My pleasure to help.

It has also happened to me before: spent hours of work and wasted hours of sleep trying to find a solution; then decided to ask for help in a forum. When I saw the answer, I could not believe I had not been able to figure that out myself. :-)

 

If you can mark my answer as the best answer, it would be great! Enjoy your weekend and have lots of fun with Excel!

Related Conversations
Excel on iPad
Mattlewis in Excel on
2 Replies
Help with excel
Alexvez01098 in Excel on
10 Replies
Rotate data from single cell to vertical listings
WEI_Bob in Excel on
2 Replies
2 Seniority Lists
Aminam20 in Excel on
9 Replies
Conditional formatting with wildcard
Rajeev_Raghavan in Excel on
2 Replies