Office 2010 Class #24: Excel Functions COUNTIF & SUMIF Count and Add with one Condition (Criteria)

[@zerovk9x]

just came across your channel when searching for some Excel tutorials and immediately amazed at the vast number of videos you've posted. I've learnt a great deal and will definitely keep watching. Thank you very much!

[@excelisfun]

I am glad that you like it!

[@excelisfun]

@prezjohnkennedy , I am glad that you like the videos!

[@excelisfun]

Upper and lower limit means there are two criteria. The solution depends on whether or not each limit is included or not. If there were both included for counting: =COUNTIFS(NumberRange,">=1000",NumberRange,"

[@jasondebaugh]

Thank you sooooo much for your entire channel. You save me many times a month. I use your tips to save hours of time.

[@bulba1]

I've watched you for some years now, hats off to you; thanks

[@excelisfun]

Download files at: people.highline.edu/mgirvin/excelisfun.htm or people.highline.edu/mgirvin/AllClasses/216_2003/216/busn216.htm

[@excelisfun]

You are soooo welcome!

[@excelisfun]

Thanks for the tip!

[@excelisfun]

@davidleebiz , you are welcome!

[@excelisfun]

In cell A1 type 0. In cell B1 type 3. In cell A2 type 4. In cell B2 type 6. Then in Excel 2007 or later use formula: =COUNTIFS(range,GreatThanOrEqualTo&A1,range,LessThanOrEqualTo&B1) In 2010 or later use: =SUMPRODUCT(--(rangeGreatThanOrEqualToA1),--(rangeLessThanOrEqualToB1))

[@excelisfun]

Yes, it is almost always better to put formula inputs into cells.

[@excelisfun]

you are welcome!

[@nightworld0092003]

once again another nice video. well structured and presented.

[@excelisfun]

Only basic videos. In this series in the up in the 40s.

[@wizard167]

Great videos. Totally love what you are doing. Learnt lots from you. Keep it up!! :)

[@Ash_Rahmani]

You are so awesome. Thank you for these videos.

[@alicec2631]

You actually make me like Excel :)

[@excelisfun]

Cool!

[@excelisfun]

KZbin is not a good place to have back and forth dialog becasue it does not allow all characters that formulas use... put comparative operators listed below in two double quotes and try formula.

[@darkmanunknown]

Awesome! I'm subscribing and going to check out your other videos!

[@excelisfun]

Glad the video helps!

[@DASDRO]

Thank you for sharing, Mike!! Quick question, is there a shortcut for format painter? Thank you!

[@gschadler]

Thanks for all the great videos. I have learned a ton of short cuts and ways to make my life easier working with excel from your tutorials. I have an extensive tracking system I am trying to create for work and was looking for some advice. Do you have an email or way to ask question without taking up a ton of space on comments through youtube?

[@AL-te3ps]

I am having the same issue with sumif? how would it be? What I have is a column with a series of figures that represents loan amounts. So I would like to get the total volume for loan

[@AL-te3ps]

it means we can use the same logic you used in the video. Having a cell with the criteria we want to refer to?

[@Zak9ty]

Cool dude, can I however do a SUMIF that includes a COUNTIF function with multiple criteria?

[@tparrott100]

Thank you so much sir!

[@excelisfun]

There probably is, but I do not know it...

[@tparrott100]

Hi Mike, Great Videos, but I have a question. How do you get excel to count events between numbers inclusive. Example: I want to know it to count people who stayed in an apartment complex: 0-3 months, 4-6 months etc.

[@aliarshad6635]

Mike, I have a question. What would be the formula of getting a sum of two cells (A1 and A2) only if the one cell meets a condition/criteria (say only if A2 is greater than 100). And that condition should be written in the formula rather than referring to a cell.

[@rucker3693]

Do you have tutorials for Access 2010?

[@AL-te3ps]

it means we can not use the criteria cell as you showed it in the video?

[@chowman007]

Mike, how about if you want to sum data that is less than 40 but greater than 0?

[@tparrott100]

Ok thanks for the help!

[@tnguyen6101989]

I am looking to get my MOS Master and your videos help a tons. Do you have any training program or prep class for MOS Excel?

[@excelisfun]

Although my videos cover most of what is on those tests, they are not set up to get a person past one of those tests.

[@Kieran6826]

Hi there, I'm struggling with an assignment, I have a column that has an amount of cars that were sold by a car dealership, can i use a COUNTIF to calculate how may cars were sold? If not, where could I use a COUNTIF, please respond, Thanks.

[@jessiejaranilla4407]

you make me smile!! what it is? alt+w+g to make it zoom?

[@excelisfun]

Yes: Alt, W, G : ) Thank you for your support with your comments, Thumbs Ups and Sub, Jessie !!!!

[@jessiejaranilla4407]

i have recently download some of your videos particularly the common excel functions that offices uses now a days, and honestly i watch it from time to time and do practice by my self here in my PC, and it's work! thank you pls. upload more videos about how to solve problem that possible we might encountered in excel..

[@jsvihla]

Hello, your channel is amazing but I have a problem. When I press F3 on my keyboard it doesn't work because it just acts as a screen light button. If I press F2 it makes the screen darker and F3 makes it lighter. All other keyboard shortcuts seem to work. Could you tell me the solution to this please? Thank you.

[@algeriaconqer]

is it important to put the criteria in " " both or one of them?

[@cilor77]

If I want to add anything greater than 8.01 how can I create the criteria ? I have 5 days in which an employee worked 8.30, 8.50, 8.12, 8.0and 8.20 how can I create the formula only to extract 8 hours for each day and the other one to add the .30,.50, .12 and .20 please help me.

[@excelisfun]

I do not know. If you are using a MAc, I don't know even more...

[@excelisfun]

SUMIFS function.

[@AL-te3ps]

SUMIF(Present_Holding,">=0"),(Present_Holding,"

[@luisaguerrabs]

If you have a Windows laptop, just press fn before pressing the f keys.

[@AL-te3ps]

How can you do if you have the criteria: 1,000 - 5,000?

[@PimpmaPC]

Why did you make a name for each column, wouldn't it be easier to treat it as one data set?

[@prezjohnkennedy]

god i wish you were my professor for excel. mine right now s-u-c-k-s.