Free exercises COUNTIF(S) and SUMIF(S) functions

I have prepared two Excel tables with data and many tasks to use COUNTIF(S) and SUMIF(S). Each task contains also the result.

Exercise 1

The first table contains Orders and Transport information. Data come from a big wholesale of electronics.

Table 1

There are some of the tasks:

Exercise1

Exercise 2

The second table contains data from Hairdressers and barbers. There is information about each customer in May 2013. What Service, Stylist, Price, Payment and when.

Table 2

Your task is to find some statistical values. The results are included on the third sheet.

Exercise2

These exercises are for free and you can use them, change them or copy them. If you find any mistake, please leave a comment.

Free download: countif sumif exercises.xlsx

Add a comment »54 comments to this article

  1. Big help, thank you.

    Reply

  2. WOW THIS HELP ME BATTLE MY DEPRESSION

    Reply

  3. THANK YOU IT’S EVERY HELPFUL

    Reply

    • arthritis

      Reply

  4. thank you

    Reply

  5. Answer of Exercise 2

    Reply

  6. =SUMIFS(E2:E25,G2:G25,G3,G2:G25,G4,G2:G25,G7)

    I am using this for the 3 criteria in sumifs but it is giving me a zero. What am I doing wrong ?

    Reply

    • Dear Bro Use add(+) symbol you will get output =SUMIFS(E2:E25,G2:G25,”Baltimore”)+SUMIFS(E2:E25,G2:G25,”NY”)+SUMIFS(E2:E25,G2:G25,”Philadelphia”)

      Reply

  7. great

    Reply

  8. T H A N K Y O U ! !
    8 years later this is still relevant, thank you so much. After thinking SUMIFS/COUNTIFS were so confusing I now understand because of this.
    I did get a different answer for Sum of price for Shaving between 5/10/2013 and 5/20/2013 because of the difference in date format

    Reply

  9. Thank you for uploading this exercise! It was fun and interesting doing it, as well as it helped me improve on my mistakes. I keep forgetting to add some ranges from time to time, however I think I will keep a better eye for that from now on!

    Reply

  10. This was a great use for my upcoming exam, thanks!

    Reply

  11. I need this practice sheet

    Reply

  12. These were good practice questions.

    Thanks 🙂

    Reply

  13. Thanks for sharing the excercise.

    Reply

  14. Hi! Than you for the exercises. However, let me ask you why do you use different types of dates in the exercises? 01.05.2013 and 5/10/2013. I think that is why you have zeros in the results, however, if you keep dates in the same type, numbers change (10.05.2013 instead of 5/10/2013).
    Thank you.

    Reply

    • Hi Maria,

      date format depends on your computer settings. If you are US located you will have probably 5/10/2013, other countries will have 10.5.2013.

      You are right that this is important for getting the correct result.

      Jan

      Reply

  15. Hi,
    I’m a distributor that get the product from a company and will sell to a distributor/ Wholesale which will sell to end-clients. I would like to play with the figures in order to determine the price for the distributor/ Wholesale can sell to the end-clients base on this price I will be able to know the efficiency/optimal price to sell to the distributor / Wholesale. can you help or do you know where to find on the web? I had hard times to find this kind of software or excel file like that.

    thanks for reply

    Arie Landsmann  

    Reply

    • Hi Arie,

      I am an experienced Financial Analyst based in Florida who is off work currently, I could help you with this – please contact me.

      Yehoshua Crisp

      Reply

  16. Thanx a lot. I need more conceptual practice problems.

    Reply

  17. Thanks a lot for sharing. I did not find as simple exercise as this. And it is for free. Great!

    Reply

  18. Is it possible to use COUNTIFS and SUMIFS in Google spreadsheets?

    Reply

    • Yes. You can use both functions in Google spreadheets. The syntax is here:
      SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

      Reply

  19. Thank you for this simple explanation. What is the difference between COUNTIF(SUMIF) and pivot table? I have a big data table and I need to create overview statistics from this source. I don’t know which way is better.

    Reply

  20. Hi
    thanks for shearing!
    Regards

    Reply

  21. Great exercise, my learners lved the challenge of working out the functions. Thanks

    Reply

  22. On exercise 2, the total price is incorrect. For example, the total times “shaving” is 71. The cost per shave is $7.00. So 71 times $7.00 is $497. Also the total price by cash is incorrect.

    Reply

    • Sorry, I just notices that not all shaves are $7.00!

      Reply

  23. PURRFECT I HAVE EXAM TMRW OF ICT N I AM IN GRADE 7!

    THIS HELPED ME A LOT!!!

    Reply

  24. How to sumif multiple ranges in excel? I have 3 criteria in the table.

    Reply

    • Hi,

      use SUMIFS function. Each Criteria range has its own criteria field. Criteria_range1 – Criteria1, Criteria_range2 – criteria2, …

      Reply

  25. Thank you so much for sharing this exercise! I found it really helpful and I feel more competent with the use of COUNTIFS and SUMIFS.

    Reply

  26. Can you show the formulas you used please

    Reply

    • Hello,

      there are results in each sheet. If you click in the cell you will see the formula.

      Reply

  27. GBFG

    Reply

  28. How can I download COUNTIFS example?

    Reply

    • Hello,

      COUNTIFS and SUMIFS examples are part of this Excel file. Click on the link Free download… to get it.

      Reply

  29. Please give me the answers for this exercises.

    Reply

    • Hello,

      answers are included in file.
      Exercise 1 has answers in column H (results).
      Exercise 2 has answers in separated sheet Exercise 2 – result.

      Reply

  30. Really simple exercise to learn how to use COUNTIFS and SUMIFS.

    Thank you.

    Reply

    • Sir Can I have the answers for this exercises please?

      Reply

  31. Thanks, with the practice of your exercises I started doing this more smoothly.
    Real thanks 🙂

    Reply

  32. VERY nice and healthy exercise for the excel beginners.

    Thanks Sir or Mam, who is behind this.
    Pradip

    Reply

  33. Thank you!!

    Reply

  34. Thank you very much Jan Podlesak. Good job!

    Reply

  35. It is a helpful and useful exercise for learner as me. I hope that you will upload more exercises which are related to all excel formulas!

    Reply

  36. Brilliant exercises! Well done and thank you! :o)j

    Reply

  37. Thanks for the quiz. It has helped my mastery of countifs and sumifs.

    Reply

  38. Really helpfull excercise……………..

    Reply

  39. I have a doubt in doing the question, (sum of items transported by trucks) And ty

    Reply

  40. hei dear, pls help me with these, i am making an excel table to get auto update in point table *meaning-when ever i put a score on match schedule list, it will automatically update in the point table
    HELP- i need to look for “TEAM A” name and if its score is greater than the “TEAM B” score then MATCHES won should reslut as”1″, like below

    A B C
    1 Team name|vs|Team name
    2 3 | | 4
    3
    4

    point table
    MP W D L GF GA GD
    TEAM A 1 0 0 1
    TEAM B 1 1 0 0

    Reply

  41. THANK YOU FOR HELPING US

    Reply

  42. Good exercise

    Reply

    • shush

      Reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress