SOLVED

Help with function COUNTIF

%3CLINGO-SUB%20id%3D%22lingo-sub-1958078%22%20slang%3D%22en-US%22%3EHelp%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958078%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20count%20the%20cells%20in%20a%20column%20that%20contain%20a%20number%20value%20but%20only%20if%20another%20cell%20in%20the%20same%20row%20contains%20the%20specified%20text.%20(possibly%20using%20wrong%20function%3F)%20see%20below%20for%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20count%20all%20the%20cells%20contain%20a%20number%20value%20by%20lets%20say%20month%20of%20Dec%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237893iC5121729F5F161CC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1958078%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958123%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958123%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20will%20need%20to%20apply%20a%20COUNTIFS%20function%20since%20you%20are%20reviewing%20more%20than%20one%20criteria.%20See%20the%20example%20below%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_1-1607125913758.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237907i33495F6F90F2058F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_1-1607125913758.png%22%20alt%3D%22adversi_1-1607125913758.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958538%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%2BCOUNTIFS(E2%3AE7%2CE11%2CF2%3AF7%2C%22%26lt%3B%26gt%3B%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20find%20attached%20screen%20shot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22370%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128%22%3EMonth%3C%2FTD%3E%3CTD%20width%3D%22242%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Enov%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Edec%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Enov%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Edec%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Enov%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Edec%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Edec%3C%2FTD%3E%3CTD%3E%3D%2BCOUNTIFS(E2%3AE7%2CE11%2CF2%3AF7%2C%22%26lt%3B%26gt%3B%22)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958775%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958775%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variants%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20431px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237983i2D854118ED7DDDB8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959007%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959007%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20all%20for%20the%20help!%20this%20works%20perfectly!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGo's%20to%20show%20I%20need%20to%20brush%20up%20on%20my%20excel%20skills%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959008%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20what%20each%20of%20us%20do%20every%20day%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959089%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959089%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20the%20last%20section%20of%20this%20excel%20sheet%20and%20I%20need%20a%20formula%20to%20figure%20the%20commissions%20earned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20needs%20to%20add%20all%20the%20number%20values%20in%20a%20column%20then%20multiply%20that%20by%20the%20commission%20lets%20say%20%24200%20per%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20found%20a%20way%20to%20do%20it%20but%20its%20a%20long%20and%20absurd%20method%20(needs%20to%20be%20cleaned%20up%20bad)%3C%2FP%3E%3CP%3EDon't%20Laugh%20but%20this%20is%20my%20poor%20attempt%20to%20try%20and%20solve%20this%20issue%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EEach%20of%20the%20purple%20cells%20contains%20a%20value%20similar%20to%20this%20(%3DC8*200)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238006iE47A1D0CF15E3132%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959112%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20but%20that's%20not%20clear%2C%20at%20least%20for%20me.%20Where%20is%20C8%2C%20is%20200%20fixed%20for%20all%20entries%20or%20variable%2C%20are%20column%20numbers%20variable%20or%20fixed%2C%20do%20you%20have%20total%20column%20in%20any%20case%20or%20that's%20just%20helper.%20In%20general%20%3D%20TotalInRow*200.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959116%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959116%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologies%2C%20I%20rushed%20that%20question%20started%20getting%20busy%20at%20the%20office.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20the%20200%20is%20fixed%20for%20all%20entry's%20in%20that%20column%20but%20it%20needs%20to%20only%20count%20the%20values%20by%20the%20specified%20month%20like%20before%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959127%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20but%20I%20don't%20see%20months%20at%20all%20on%20your%20screenshot.%20Could%20you%20please%20give%20bit%20more%20details%2C%20desirably%20with%20sample%20file%2C%20when%20you%20have%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959261%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20sample%20of%20the%20doc%20I'm%20working%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20tracker%20page%20I%20am%20trying%20to%20calculate%20the%20%24200%20commissions%20earned%20for%20each%20home%20on%20the%20data%20page%20by%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959285%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891655%22%20target%3D%22_blank%22%3E%40AaronC92%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%2C%20but%20file%20doesn't%20explain%20the%20logic%20for%20me.%20Why%20for%20Jan%20you%20calculate%20sum%20of%20working%20hours%20*%20200%2C%20but%20for%20Nov%20Changes*2%3F%20I'm%20not%20in%20your%20business%20and%20have%20no%20idea%20what's%20that%20about%2C%20if%20you%20explain%20more%20close%20to%20Excel%20terms%20that%20will%20be%20great.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959288%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20purple%20is%20old%20and%20is%20no%20longer%20correct%2C%20so%20yeah%20that%20wont%20make%20any%20sense%20just%20ignore%20that%20completely.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20need%20a%20formula%20to%20take%20all%20the%20values%20in%20the%20home%20column%20in%20the%20specified%20month%20like%20Jan%20or%20any%20month%20and%20multiply%20that%20by%20%24200%20to%20replace%20the%20no%20longer%20working%20formula%20in%20the%20%22Estimated%20Monthly%20Commission%20Total%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20I%20have%20a%20working%20formula%20for%20that%20I'm%20sure%20I%20can%20edit%20to%20fit%20for%20the%20other%20months%20and%20change%20the%20commission%20rate%20for%20the%20different%20products.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helped%20clear%20things%20up%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1959290%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20function%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959290%22%20slang%3D%22en-US%22%3E%3CP%3EAt%20this%20point%20I%20can%20just%20delete%20the%20purple%20mess%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to count the cells in a column that contain a number value but only if another cell in the same row contains the specified text. (possibly using wrong function?) see below for example

 

I need to count all the cells contain a number value by lets say month of Dec

 

 

Capture.PNG

16 Replies

@AaronC92 

You will need to apply a COUNTIFS function since you are reviewing more than one criteria. See the example below:

adversi_1-1607125913758.png

Best Response confirmed by AaronC92 (Occasional Contributor)
Solution

@AaronC92

 

=+COUNTIFS(E2:E7,E11,F2:F7,"<>")

 

please find attached screen shot

 

Month 
nov1
dec1
nov 
dec 
nov2
dec2
  
  
  
dec=+COUNTIFS(E2:E7,E11,F2:F7,"<>")

 

@AaronC92 

As variants

image.png

Thank you all for the help! this works perfectly! 

 

Go's to show I need to brush up on my excel skills

@AaronC92 

That's what each of us do every day

I am working on the last section of this excel sheet and I need a formula to figure the commissions earned.

 

It needs to add all the number values in a column then multiply that by the commission lets say $200 per entry.

 

I have found a way to do it but its a long and absurd method (needs to be cleaned up bad)

Don't Laugh but this is my poor attempt to try and solve this issue

Each of the purple cells contains a value similar to this (=C8*200)

Capture.PNG

@AaronC92 

Sorry, but that's not clear, at least for me. Where is C8, is 200 fixed for all entries or variable, are column numbers variable or fixed, do you have total column in any case or that's just helper. In general = TotalInRow*200.

@Sergei Baklan 

I apologies, I rushed that question started getting busy at the office.

 

Yes, the 200 is fixed for all entry's in that column but it needs to only count the values by the specified month like before

@AaronC92 

Sorry, but I don't see months at all on your screenshot. Could you please give bit more details, desirably with sample file, when you have time.

@Sergei Baklan 

 

Here is a sample of the doc I'm working on.

 

On the tracker page I am trying to calculate the $200 commissions earned for each home on the data page by month.

@AaronC92 

Thank you, but file doesn't explain the logic for me. Why for Jan you calculate sum of working hours * 200, but for Nov Changes*2? I'm not in your business and have no idea what's that about, if you explain more close to Excel terms that will be great.

@Sergei Baklan 

 

The purple is old and is no longer correct, so yeah that wont make any sense just ignore that completely.

 

I just need a formula to take all the values in the home column in the specified month like Jan or any month and multiply that by $200 to replace the no longer working formula in the "Estimated Monthly Commission Total"

 

Once I have a working formula for that I'm sure I can edit to fit for the other months and change the commission rate for the different products.

 

Hope that helped clear things up 

At this point I can just delete the purple mess

@AaronC92 

If like this that's

=SUMIFS(Table3[Home],Table3[Date],Tracker!B4)*200

@Sergei Baklan 

 

This is exactly what I needed! thank you

@AaronC92 , you are welcome