Solved

Excel Sumif Question

Posted on 2013-11-07
9
308 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
RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

 
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

Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

737 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