Solved

Excel Sumif Question

Posted on 2013-11-07
9
280 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
9 Comments
 
LVL 21

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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now