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

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

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


 


 

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