Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

countif or sumif formula tricky one

Posted on 2014-02-10
6
Medium Priority
?
238 Views
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
0
Comment
Question by:route217
[X]
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
6 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 39846880
a bit not clear
counting results between set dates but see no dates here.
then
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

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

can you post a sample workbook ?
gowflow
0
 

Author Comment

by:route217
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. ..
0
 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 1000 total points
ID: 39846935
Hi,

pls try

=SUMIF(A1:A7,"<=3",B1:B7)

Open in new window

and
=COUNTIF(A1:A7,"<=3")

Open in new window

Regards
Sumif3.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 31

Accepted Solution

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

for >=1 and <=3 this is the formula
=SUMIFS(B2:B8,A2:A8,">=1",A2:A8,"<=3")

for<4 this is the formula
=SUMIFS(B2:B8,A2:A8,"<4")

and you can see it in the attached file.
gowflow
SUMIFS.xlsx
0
 

Author Comment

by:route217
ID: 39846948
Gowflow excellent feedback. ..how would I do the count..
0
 
LVL 31

Expert Comment

by:gowflow
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
=COUNTIFS(A1:A7,"<=3")

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.
gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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