Solved

Excel Sumif Question

Posted on 2013-11-07
9
289 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
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…

776 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