SUMIF and YEAR

G

Guest

My data looks like this

A B
1 Date Acquired Memo Cos
2 02/17/97 Computer 1,000.00
3 09/11/97 Video 15.040
4 09/13/97 Sign 105.54
5 04/08/98 Desk 150.00
6 07/23/98 Chair 75.00
7 1/1/00 Table 60.00
8 3/12/00 File 40.00
9 6/17/01 Light 65.00
10 10/11/01 Cube 850.00
11 12/4/02 Bin 125.00
1
13 Year
14 1997 1,120.54
15 1998 225.00
16 1999
17 2000 100.00
18 2001 850.00
19 2002 125.00

I am trying to write a SUMIF formula that will examine cells A1:A11 and if the YEAR in those cells is equal to YEAR in say cell A14 then it will sum the appropriate cells in C1:C11 and return a value of 1,120.54

The formula I am trying to write in cell C14 looks like this

=SUMIF(YEAR(A1:A11),?????,C1:C11

I am stuck on the ????? part.

Any thoughts

Thank
 
P

Peo Sjoblom

One way

=SUMPRODUCT(--(YEAR($A$2:$A$11)=A14),$C$2:$C$11)

copy down

--

Regards,

Peo Sjoblom

MIKE said:
My data looks like this:

A B C
1 Date Acquired Memo Cost
2 02/17/97 Computer 1,000.00
3 09/11/97 Video 15.040
4 09/13/97 Sign 105.54
5 04/08/98 Desk 150.00
6 07/23/98 Chair 75.00
7 1/1/00 Table 60.00
8 3/12/00 File 40.00
9 6/17/01 Light 65.00
10 10/11/01 Cube 850.00
11 12/4/02 Bin 125.00
12
13 Year
14 1997 1,120.54
15 1998 225.00
16 1999 0
17 2000 100.00
18 2001 850.00
19 2002 125.00


I am trying to write a SUMIF formula that will examine cells A1:A11 and if
Click to expand...
the YEAR in those cells is equal to YEAR in say cell A14 then it will sum
the appropriate cells in C1:C11 and return a value of 1,120.54.
 
N

Norman Harker

Hi Mike!

Try:

C14:
=SUMPRODUCT((YEAR($A$2:$A$11)=A14)*($C$2:$C$11))
Copy down

BTW your 2001 was 915.00
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Naraine

your ????? is the year i.e 1997,1998 etc.

=SUMIF(YEAR(A1:A11),1997,C1:C11)

=SUMIF(YEAR(A1:A11),1998,C1:C11)
etc.




MIKE said:
My data looks like this:

A B C
1 Date Acquired Memo Cost
2 02/17/97 Computer 1,000.00
3 09/11/97 Video 15.040
4 09/13/97 Sign 105.54
5 04/08/98 Desk 150.00
6 07/23/98 Chair 75.00
7 1/1/00 Table 60.00
8 3/12/00 File 40.00
9 6/17/01 Light 65.00
10 10/11/01 Cube 850.00
11 12/4/02 Bin 125.00
12
13 Year
14 1997 1,120.54
15 1998 225.00
16 1999 0
17 2000 100.00
18 2001 850.00
19 2002 125.00


I am trying to write a SUMIF formula that will examine cells A1:A11 and if
Click to expand...
the YEAR in those cells is equal to YEAR in say cell A14 then it will sum
the appropriate cells in C1:C11 and return a value of 1,120.54.
 
H

Harlan Grove

Thanks. Is there a reason why the SUMIF function would not work?
Click to expand...

One SUMIF won't work because you're not selecting a single value (=) within the
date range but rather selecting a range of date values that fall in a single
year. SUMIF (and COUNTIF) accept *ONLY* direct references to single area ranges.
So SUMIF(A1:A99,x) works because A1:A99 is a single area range, but
SUMIF(YEAR(A1:A99),x) fails because YEAR(A1:A99) isn't a reference to a range.

You could do this with two SUMIF calls each making a DATE call.

=SUMIF(A1:A11,">="&DATE(1997,1,1),C1:C11)
-SUMIF(A1:A11,">"&DATE(1998,1,1),C1:C11)

However, a single SUMPRODUCT call would be more efficient.
 
N

Norman Harker

Hi Naraine!

Your suggestion doesn't work. Either use Harlan's approach to SUMIF
or, as everyone has recommended, use SUMPRODUCT.

An alternative is:

=SUM(IF(YEAR($A$2:$A$11)=A14,1)*$C$2:$C$11)
Entered as an array by pressing and holding down Ctrl+Shift and then
pressing Enter.

Although SUMPRODUCT involves an array, there is a tendency to prefer
it's non-array entered approach.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
P

Paul

MIKE said:
My data looks like this:

A B C
1 Date Acquired Memo Cost
2 02/17/97 Computer 1,000.00
3 09/11/97 Video 15.040
4 09/13/97 Sign 105.54
5 04/08/98 Desk 150.00
6 07/23/98 Chair 75.00
7 1/1/00 Table 60.00
8 3/12/00 File 40.00
9 6/17/01 Light 65.00
10 10/11/01 Cube 850.00
11 12/4/02 Bin 125.00
12
13 Year
14 1997 1,120.54
15 1998 225.00
16 1999 0
17 2000 100.00
18 2001 850.00
19 2002 125.00


I am trying to write a SUMIF formula that will examine cells A1:A11 and if
Click to expand...
the YEAR in those cells is equal to YEAR in say cell A14 then it will sum
the appropriate cells in C1:C11 and return a value of 1,120.54.
The formula I am trying to write in cell C14 looks like this:

=SUMIF(YEAR(A1:A11),?????,C1:C11)

I am stuck on the ????? part.


Any thoughts?

Thanks
Click to expand...

Just to add to previous replies what might be helpful to you for the future,
SUMIF (and COUNTIF) are quite simple functions. The first parameter must be
a range (plain and simple); it cannot be a function of a range. So you can
start thus:
=SUMIF(A1:A11..........
but you know immediately that there will be a problem if you are tempted to
start thus:
=SUMIF(YEAR(A1:A11..........

SUMPRODUCT is a much more versatile function. It will do everything that
SUMIF and COUNTIF will do, without the limitation described above, and can
also have more than one criterion.
 
N

Naraine

Norman,
you are correct. However, the following works.

A B C D E
1 Date Acquired Memo Cost Year
2 02/17/97 Computer 1,000.00 1997
3 09/11/97 Video 15.04 1997
4 09/13/97 Sign 105.54 1997
5 04/08/98 Desk 150.00 1998
6 07/23/98 Chair 75.00 1998
7 01/01/00 Table 60.00 2000
8 03/12/00 File 40.00 2000
9 06/17/01 Light 65.00 2001
10 10/11/01 Cube 850.00 2001
11 12/04/02 Bin 125.00 2002

Year Amt
1997 1,120.58 =SUMIF($E$3:$E$12,A15,$D$3:$D$12)
1998 225.00 =SUMIF($E$3:$E$12,A16,$D$3:$D$12)
1999 - =SUMIF($E$3:$E$12,A17,$D$3:$D$12)
2000 100.00 =SUMIF($E$3:$E$12,A18,$D$3:$D$12)
2001 915.00 =SUMIF($E$3:$E$12,A19,$D$3:$D$12)
2002 125.00 =SUMIF($E$3:$E$12,A20,$D$3:$D$12)
Total 2485.58
 
N

Norman Harker

Hi Naraine!

Confirmed that it works OK with the additional helper column
extracting the year from the date column.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

John Drummond

Sounds like you've sorted this, and I hope I am not out of line for
plugging, but if you routinely do this sort of thing, the free of version of
my add-in at www.businessfunctions.co.uk has a function called MkPmts(Time,
Base, PmtDates, Pmts) that does just this job.
 
N

Norman Harker

Hi John!

Re: "I hope I am not out of line for plugging."

Not if it's free! At least that's my general understanding.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

John Drummond

Sorry to correct you, but actually, if you look, the 93 function Basic
Edition containing the function I described is clearly free, without
limitation.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modifying a complex date formual to count months 4
sumif for months and year 2
Time query 9
Peak call times/hours 2
SUMIF or COUNTIF 1
Time and the Max Function 15
SUMIF/OFFSET Query 4
counting items within a date range 1

Top