countif or sumif formula tricky one

Posted on 2014-02-10
Last Modified: 2014-02-10
Hi Experts excel 2007

I am trying to count the number of results between set dates..I have the following table.

col A.         Col B. Case 1.       Col C Result
1.                             6.                  Sum of values falling into 1-3 cat based on colA
2.                             5
3.                             8
4.                             1.                  Sum of val <4 based on col A
5.                             2
6.                             9
7.                            10
Question by:route217
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 31

Expert Comment

ID: 39846880
a bit not clear
counting results between set dates but see no dates here.
Sum of values falling into 1-3 cat based on colA
of what of Col B ?

 Sum of val <4 based on col A
also not clear

Col A is figures 1,2,3,4 etc.... ? or  ? what is the point for ?

can you post a sample workbook ?

Author Comment

ID: 39846888
Hi gowflow

question1. Sum of values falling into 1-3 cat based on colAof what of Col B ?
answer. 6+5+8=19...
I am using col a as a ref table.

Also the count would be 3 records. ..
LVL 52

Assisted Solution

Rgonzo1971 earned 250 total points
ID: 39846935

pls try


Open in new window


Open in new window

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 31

Accepted Solution

gowflow earned 250 total points
ID: 39846942
ok here it is you should use SUMIFS

for >=1 and <=3 this is the formula

for<4 this is the formula

and you can see it in the attached file.

Author Comment

ID: 39846948
Gowflow excellent feedback. would I do the count..
LVL 31

Expert Comment

ID: 39846949
Well read it again and although you said SUM of values for <4 but then you give the result of 3 then it is no more a SUMIFS but a COUNT

so for <4

You can use them without the S at the end as well but I prefer these as they open the door for multiple criteria if and when needed.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question