• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Excel Sumif Question

My criteria column has dates. I would like to sum the adjacent column based on the year.
Thank you.
0
rjfields
Asked:
rjfields
2 Solutions
 
Ejgil HedegaardCommented:
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
 
NBVCCommented:
Try Sumproduct

e.g.

=SUMPRODUCT(--(YEAR(A1:A10)=2013),B1:B10)
0
 
NBVCCommented:
Or if you want SUMIFS

e.g. =SUMIFS(B1:B10,A1:A10,">=1/1/2013",A1:A10,"<=12/30/2013")
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tel2Commented:
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
 
NBVCCommented:
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
 
rjfieldsAuthor Commented:
Thank you very much.
0
 
tel2Commented:
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
 
NBVCCommented:
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
 
tel2Commented:
OK - thanks NB_VC!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now