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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies