Solved

Excel Sumif Question

Posted on 2013-11-07
9
311 Views
Last Modified: 2013-11-08
My criteria column has dates. I would like to sum the adjacent column based on the year.
Thank you.
0
Comment
Question by:rjfields
[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
9 Comments
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 250 total points
ID: 39631478
Use an array formula
=SUM(IF(YEAR(A1:A10)=2013,B1:B10,0))
Dates in range A1:A10 and the values to sum in B1:B10
Enter the formula with Ctrl+Shift+Enter
0
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 250 total points
ID: 39631485
Try Sumproduct

e.g.

=SUMPRODUCT(--(YEAR(A1:A10)=2013),B1:B10)
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39631491
Or if you want SUMIFS

e.g. =SUMIFS(B1:B10,A1:A10,">=1/1/2013",A1:A10,"<=12/30/2013")
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 12

Expert Comment

by:tel2
ID: 39631648
Hi NB_VC,

What a creative way of using SUMPRODUCT!

What's the '--' about, and where can we read about it?  (I'm struggling to Google for '--').
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39631673
It's called a "double unary".  It is basically a double negative.  It coerces the results of the condition (which normally gives TRUE/FALSE values) into 1/0 results respectively.  This way the SUMPRODUCT can do it's math.

There are other ways to:

for example

SUMPRODUCT(1*(YEAR()...

or SUMPROCUCT(0+(YEAR().....
0
 

Author Closing Comment

by:rjfields
ID: 39631798
Thank you very much.
0
 
LVL 12

Expert Comment

by:tel2
ID: 39632105
Thanks for that explanation, NB_VC.

Well said, but I still don't fully understand the need for it, because if I put 1/1/2013 in A1, these work as is:
    =(YEAR(A1)=2013)*3      => Result is 3
    =(YEAR(A1)=2012)*3      => Result is 0
Any ideas why the above work as is, but SUMPRODUCT() with ranges needs the '--'?

I'm using Excel 2003.  All the latest, a decade ago.

PS: I don't really need this, but wouldn't mind understanding.

Thanks.
tel2
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39633252
The * in *3 is actually coercing the (YEAR(A1)=2013) from TRUE to 1, then it multiplies by 3.  When you use operators like * , + , - , /, then the need for the -- is eliminated, because those operators are actually doing the coercing.  Remember the -- is a double negative, so it with the first - it coerces and converts the 1's to -1's, so you need another - to revert them back to +1's.
0
 
LVL 12

Expert Comment

by:tel2
ID: 39634637
OK - thanks NB_VC!
0

Featured Post

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!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

707 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