Count IF

%3CLINGO-SUB%20id%3D%22lingo-sub-3120418%22%20slang%3D%22en-US%22%3ECount%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3120418%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20what%20I%20need%20to%20do%20%3A%3C%2FP%3E%3CP%3EOn%20B1%2C%20formula%20that%20allows%20me%20to%20get%20a%20total%20as%20soon%20as%20as%20CONFIRMED%20is%20mention%20on%20the%20column%20B.%3C%2FP%3E%3CP%3EIf%20column%20B%20is%20on%20confirmed%2C%20total%20of%20all%20K%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20one%20can%20help%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%26nbsp%3B%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%22jpilard_0-1644064989629.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345527i3F66C1AA36411E1D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22jpilard_0-1644064989629.png%22%20alt%3D%22jpilard_0-1644064989629.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3120418%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3120474%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3120474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1297951%22%20target%3D%22_blank%22%3E%40jpilard%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20I%20recommend%20to%20provide%20more%20information.%3C%2FP%3E%3CP%3EYou'll%20get%20a%20quicker%20and%20more%20accurate%20answer%2C%20and%20those%20who%20want%20to%20help%20don't%20have%20to%20invest%20time%20guessing%20the%20problem.%3C%2FP%3E%3CP%3EI%20recommend%3A%20Always%20inform%20about%20your%20Excel%20version%2C%20operating%20system%2C%20storage%20medium%2Fhard%20drive%2C%20OneDrive%2C%20Sharepoint%2C%20etc.).%3C%2FP%3E%3CP%3EIf%20possible%2C%20add%20a%20file%20(without%20sensitive%20data)%20and%20use%20this%20file%20to%20describe%20your%20project%20step%20by%20step%2C%20or%20add%20photos%20with%20the%20appropriate%20description.%3C%2FP%3E%3CP%3EDon't%20forget%20that%20not%20every%20Excel%20user%20has%20a%20clue%20about%20every%20job%20and%20what%20you%20see%20he%20can't%20see.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20link%20you%20will%20find%20some%20more%20information%20about%20it%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fwelcome-to-your-excel-discussion-space%2Fm-p%2F2204395%22%20target%3D%22_blank%22%3EWelcome%20to%20your%20Excel%20discussion%20space!%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3120526%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3120526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1297951%22%20target%3D%22_blank%22%3E%40jpilard%3C%2FA%3E%26nbsp%3BNot%20sure%20about%20your%20cell%20and%20column%20references%20but%20I%20guess%20that%20you%20want%20a%20formula%20in%20F1%20that%20sums%20amounts%20from%20column%20K%20in%20case%20cells%20in%20column%20D%20ae%20equal%20to%20%22CONFIRMED%22.%20If%20so%2C%20try%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMIF(D%3AD%2C%22CONFIRMED%22%2CK%3AK)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3120575%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3120575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThanks%20a%20lot%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20about%20if%20i%20have%202%20criteria%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

Here what I need to do :

On B1, formula that allows me to get a total as soon as as CONFIRMED is mention on the column B.

If column B is on confirmed, total of all K column

 

Any one can help ?

 

Thanks,  

 

 

jpilard_0-1644064989629.png

 

8 Replies

@jpilard 

With your permission, I recommend to provide more information.

You'll get a quicker and more accurate answer, and those who want to help don't have to invest time guessing the problem.

I recommend: Always inform about your Excel version, operating system, storage medium/hard drive, OneDrive, Sharepoint, etc.).

If possible, add a file (without sensitive data) and use this file to describe your project step by step, or add photos with the appropriate description.

Don't forget that not every Excel user has a clue about every job and what you see he can't see.

 

In this link you will find some more information about it:

Welcome to your Excel discussion space!

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

@jpilard Not sure about your cell and column references but I guess that you want a formula in F1 that sums amounts from column K in case cells in column D ae equal to "CONFIRMED". If so, try this:

 

=SUMIF(D:D,"CONFIRMED",K:K)

 

 

 

@Riny_van_Eekelen Thanks a lot,

 

What about if i have 2 criteria ?

@jpilard You may start by giving the complete picture. What second criteria?

 

Perhaps you can share your sheet via dropbox or something similar. Or, when you upload a screenshot, include the row and column headers so that it is clear how your schedule looks like and include examples of what you would want a formula to return in such examples.

@Riny_van_Eekelen 

 

https://www.dropbox.com/scl/fi/3s9zlaetdpdsrc2orygj2/Events-Planner-2022-1.xlsx?dl=0&rlkey=v2jromw6k...

 

With your help i have managed to get the overall amount based on criteria

 

CONFIRMED / PAIDAED 265,000.00
INQUIERYAED 0.00
CANCELAED 75,000.00

 

Now whatever is confirmed or Inquiry or Cancel I would like to get it breakdown per outlets

 

PRIVILEGECARNAFI'LIASBAR
    

 

Thanks a lot,

@jpilard  Thanks! That helps. Then you'd need SUMIFS. See attached. I'll leave the formatting to you

Riny_van_Eekelen_0-1644073364527.png

 

Thanks a lot,

Very complicated to understand but it works.

Really appreciate

@jpilard Great! But then again, the set-up of your schedule isn't optimal.