Formula: Search for Dates and Sum values

[Maxime]

Good day,

I have the following data sample:

Dates Risk Profits
16-Feb-04 Certain 0.0060
16-Feb-04 Uncertain 0.0060
16-Feb-04 Certain -0.0030
19-Feb-04 Certain 0.0060
20-Mar-04 Certain 0.0060
21-Mar-04 Certain -0.0040
22-Mar-04 Certain 0.0060
22-Mar-04 Certain 0.0060
22-Mar-04 Certain 0.0060
25-Mar-04 Certain -0.0040
08-Apr-04 Certain 0.0060
09-Apr-04 Certain -0.0040


1- I would like to be able to get the "Profits" average
per month. So how can I SUM the values from the 3rd
column for an entire month?

2- Also, per month, calculate the number of rows where
the value in the "Profits" column is >0.

Many thanks in advance.

Regards.

Maxime

 

[Norman Harker]

Hi Maxime!

=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),$C$2:$C$14)
and
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--($C$2:$C$14>0),$C$2:$C$14)

--
--
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.

 

[Maxime]

Mr. Harker.

Many thanks for your reply,

1- However, my real data spreads over several year. I
should have reflected that in my sample data. SORRY.

You provided me with the formula: =SUMPRODUCT(--(MONTH
($A$2:$A$14)=2),$C$2:$C$14)

In this case, the formula does exactly what I want,
except that it adds the values for all the February
months. It SUM all the data for Feb 1997, 1998, 1999
etc. How can I specify which month AND year to SUM the
profit columns?

2- In the case of the second formula, I actually want to
know How many rows for any given month have values over 0
or under 0. The formula you provided me SUM those
values. I tried with a COUNT but it did not work. Any
idea?

You help is very appreciated.

Maxime

 

[Norman Harker]

Hi Maxime!

No problems:

The same principles are involved.

Use:
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)=2004,$C$2:$C$14)
and
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)=2004,--($C$2:$C$14>0
),$C$2:$C$14)

But two additional points.

It might be best to use cell references raher than hard coding the month and
year numbers. And you might look at Pivot tables as a better solution to
this type of problem.

Try and understand the formulas and you'll find it easier to adapt to
changing needs. In the first formula above the first two arguments within
the SUMPRODUCT are implicit IF statements that return TRUE or FALSE. We have
to precede with the -- so that they are coerced to 1 for TRUE and 0 for
FALSE. Only if both arguments are TRUE (1) will you get 1*1*Profit and thus
cause an addition to the sum of the products of the three arguments.

P.S. Only magistrates call me Mr. !!!
--
--
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.

news:[email protected]...

 

[Maxime]

Hi Norman !

Your first formula works like a charm.
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)
=2004,$C$2:$C$14)

Your second one too, but does not do what I need.
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)
=2004,--($C$2:$C$14>0,$C$2:$C$14)

I want to know, for Feb 02 for example, HOW MANY ROWS,
meet the conditions ">0". I do not want the sum of the
rows, but rather the frequency to which the condition >0
is met.

Thanks for your help. I am very impressed.

Maxime
PS: I am not good enough with pivot table. Moreover, the
sample data I showed you in the first e-mail is a
simplified version of the real spreadsheet. A pivot
table might do the trick. But I really don't know how.
Morevoer, with the formula you provided me, I am almost
done what I need to do.

 

[Frank Kabel]

Hi
use
=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)
=2004,--($C$2:$C$14>0))

 

[Norman Harker]

Hi Maxime!

To just do a count of C2:C14 >0

=SUMPRODUCT(--(MONTH($A$2:$A$14)=2),--(YEAR($A$2:$A$14)=2004,--($C$2:$C$14>0))

Note here that I have just deleted the end argument leaving three
implicit IF statements if all three are TRUE the -- coerces them to 1
to give me 1*1*1 which is added to the sum of the products. If 1 or
more are FALSE I get (e.g) 0*1*1 which is 0 and zero added to the sum
of the products doesn't do a lot to it.


--
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.

 

[Maxime]

Hello Again,

It works great. Your help is very appreciated. Keep up
the good work.

Max