SUMIF with YEAR criteria

H

hmm

=SUMIF($B$4:$B$300,????????,$D$4:$D$300)

I want the criteria (where the ?????s are) to be based on the year in
cells B4:B300.

Thanks
 
N

Norman Harker

Hi hmm!

Try:

=SUMIF($B$4:$B$300,"="&2003,$D$4:$D$300)

But if your column contains dates rather than year numbers you would
need something like:

=SUMPRODUCT(($F$4:$F$300<>"")*(TEXT($F$4:$F$300,"yyyy")="2003")*($H$4:
$H$300))


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
H

hmm

Why doesn't this work?:

{=SUM(IF((B4:B300>=DATE(2003,1,1))+(B4:B300<=DATE(2003,12,31)),D4:D300))}

It sums dates outside of the range in the formula...
 
N

Norman Harker

Hi hmm

Because you're asking it, in effect to sum every date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi hmm!

That's what I did with the formula I suggested:

=SUMPRODUCT(($F$4:$F$300<>"")*(TEXT($F$4:$F$300,"yyyy")="2003")*($H$4:
$H$300))

You're looking for all of H4:H300 where the date in F4:F300 is in the
year 2003

(TEXT($F$4:$F$300,"yyyy") strips out the four digit year number from
those dates as text

(TEXT($F$4:$F$300,"yyyy")="2003" returns true if the text string from
the TEXT function is 2003. SUMPRODUCT coerces the true to 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
H

hmm

Got it! I don't know what I was doing wrong the first time I tried the
SUMPRODUCT formula yesterday, but now it's working.

Thanks alot & happy new year.
 
N

Norman Harker

Hi hmm!

Good to get confirmations as they show Google searchers that solutions
work "as advertised"

Happy New Year

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
H

Harlan Grove

...
...
But if your column contains dates rather than year numbers you would
need something like:

=SUMPRODUCT(($F$4:$F$300<>"")*(TEXT($F$4:$F$300,"yyyy")="2003")
*($H$4:$H$300))
Click to expand...

The ($F$4:$F$300<>"") condition is unnecessary. Blank cells would evaluate in
the TEXT call to "1900", which won't equal "2003".
 
H

Harlan Grove

Why doesn't this work?:

{=SUM(IF((B4:B300>=DATE(2003,1,1))+(B4:B300<=DATE(2003,12,31)),D4:D300))}
Click to expand...
...

FWIW, multiply (B4:B300>=DATE(2003,1,1)) and (B4:B300<=DATE(2003,12,31), don't
add them.
 
J

Jim333

Hello Norman Harker,

First of all, I would like to thank you so much for the effort you
exert to satisfy all members.

I have one question about "SUMIF" function that have puzzled me so much
and for a long time.

As I know about "IF" function is that I can combine more than one
condition in the same formula by using "AND".

Such as: =IF(AND(C52=AP9,G52="On-Hand"),1,"")

Can I do the same thing with "SUMIF" Function? In other words, Can I
create a "SUMIF" formula containing "AND" or any other functions
performing the same job?

I already created three formulas as follows and they are working very
well, but my question is:
Can I combine the three formulas in one?

1. =SUMIF(A7:A12,G7,D7:D12)
2. =SUMIF(B7:B12,M7,D7:D12)
3. =SUMIF(C7:C12,O6,D7:D12)

If it can't be done with SUMIF,, Is there any other excel function can
do me the job??

I attached the book I want the formula to work with in order for you to
get more explanation.

I hope that my question is clear, so that you can answer me

Thank you,

Attachment filename: o&md overtime database 2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=414492
 
N

Norman Harker

Hi Jim333!

I'm afraid that neither SUMIF nor COUNTIF will accept multiple
conditions.

You can sometimes use two of them in a formula to achieve the same
objective. So you might use two for handling >7 and <10 But that's not
using multiple conditions in the function; it's using multiple
functions.

So it's down to our friends SUMPRODUCT and SUM. Once you master
SUMPRODUCT it's not too difficult and in some ways if using 3 or four
conditions, you might find it easier. I think the real clue to
understanding though is that the internal "condition" elements of the
SUMPRODUCT arguments are implicit IF statements that resolve to TRUE
(1) or FALSE (0). Only if the conditions are all TRUE will the result
be counted or summed.

Take a tip and watch out for a few questions and see if you can solve
them. Practice makes perfect unless Murphy steps in.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

Jim333

Thank you so much for you fast reply

As a matter of fact, I don't know the main purpose of the function
SUMPRODUCT
 
F

Frank Kabel

Hi

for your example
1. =SUMIF(A7:A12,G7,D7:D12)
2. =SUMIF(B7:B12,M7,D7:D12)
3. =SUMIF(C7:C12,O6,D7:D12)
Click to expand...

use the following SUMPRODUCT function
=SUMPRODUCT((A7:A12=G7)*(B7:B12=M7)*(C7:C12=O6)*(D7:D12))

this will sum als entries in column D where A=G7 AND B=M7 AND C=O6

HTH
Frank
 
N

Norman Harker

Hi Jim333!

Fast replies is what these groups are known for!

Main purpose of the function SUMPRODUCT? Probably not what we might
expect when we first look at it. I know that I was using it for other
purposes first.

SUMPRODUCT's official description is that it returns the sum of the
products of corresponding array components. In a typical case we might
have two (or more) columns of numbers and want to sum the products of
each pair (or more) numbers in each row. Internally the function
multiplies together the pairs (or more) and sums up the answers.

We adapt this feature to allow counting or summing based upon
conditions. We use arrays that contain conditions that resolve to TRUE
or FALSE and the multiplication of those results produces 1 if both
conditions are TRUE and 0 if either or both are FALSE. In it's
"counting" we are just summing the results of those multiplications.
In its "summing" form there is an addition argument that is the range
that contains the numeric data. In that for the result of the
multiplication of the conditions is applied to the corresponding data.
If all conditions are TRUE then 1 * Data means that it gets added. If
any condition is FALSE then 0 * Data means it doesn't get added.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

Jim333

Thank you so much Frank Kabel

Your SUMPRODUCT Function is working propeerly and it is what I hav
been looking for since a long period.

Thank you again

by
 
J

Jim333

Hi group,

Thank you so much Frank Kabel, the SUMPRODUCT formula you gave me i
working properly with me,

I have been looking for that formula for a long time, I reall
appreciate that for you Frank.

I actually was not aware of SUMPRODUCT function before, it is a ver
important and useful function.

Thank you so much again Frank and thank you for you Norman Harker too.

Jim33
 
Top