Two questions-SUMPRODUCT & IF something
 

Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
No. of deals 1billion plus
2002
2003

DATA
Col.A Col.D
YEAR Value in $Millions


Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

Two questions-SUMPRODUCT & IF something
 

I'm not sure what your first question is - does the SP formula not
work, then? Check the formatting of column A (dates?) and column D if
not.

For your second question, you can just reformat the cells using a
custom format of mm yyyy, or mmm yy, or mmmm yy. (3 m's will give the
abbreviated name, 4 m's will give the full name)

Alternatively, you could put:

=A1

in an adjacent cell, format this as mm yyyy (or one of the others),
and copy down.

Another alternative still is to use this formula:

=TEXT(A1,"mm yyyy")

and copy down, although this will return a text result.

Hope this helps.

Pete

On Mar 13, 3:47*pm, J.Mart wrote:
Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
* * * * * * * No. of deals 1billion plus * * *
* * * 2002
* * * 2003

DATA
Col.A * * * *Col.D
YEAR * * * *Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

Two questions-SUMPRODUCT & IF something
 

I'd use:
=SUMPRODUCT(--(text($A$18:$A$3295,"yyyymm")=text($C4,"yyyymm")),
--($D$18:$D$3295=1000))

Where C4 contains a real date, too.

J.Mart wrote:

Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
No. of deals 1billion plus
2002
2003

DATA
Col.A Col.D
YEAR Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

--

Dave Peterson

Two questions-SUMPRODUCT & IF something
 

Dave- thank you. This was helpful.

"Dave Peterson" wrote:

I'd use:
=SUMPRODUCT(--(text($A$18:$A$3295,"yyyymm")=text($C4,"yyyymm")),
--($D$18:$D$3295=1000))

Where C4 contains a real date, too.

J.Mart wrote:

Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
No. of deals 1billion plus
2002
2003

DATA
Col.A Col.D
YEAR Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

--

Dave Peterson

Two questions-SUMPRODUCT & IF something
 

Thanks!

"Pete_UK" wrote:

I'm not sure what your first question is - does the SP formula not
work, then? Check the formatting of column A (dates?) and column D if
not.

For your second question, you can just reformat the cells using a
custom format of mm yyyy, or mmm yy, or mmmm yy. (3 m's will give the
abbreviated name, 4 m's will give the full name)

Alternatively, you could put:

=A1

in an adjacent cell, format this as mm yyyy (or one of the others),
and copy down.

Another alternative still is to use this formula:

=TEXT(A1,"mm yyyy")

and copy down, although this will return a text result.

Hope this helps.

Pete

On Mar 13, 3:47 pm, J.Mart wrote:
Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
No. of deals 1billion plus
2002
2003

DATA
Col.A Col.D
YEAR Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!

Two questions-SUMPRODUCT & IF something
 

You're welcome.

Pete

On Mar 14, 7:37*pm, J.Mart wrote:
Thanks!



"Pete_UK" wrote:
I'm not sure what your first question is - does the SP formula not
work, then? Check the formatting of column A (dates?) and column D if
not.

For your second question, you can just reformat the cells using a
custom format of mm yyyy, or mmm yy, or mmmm yy. (3 m's will give the
abbreviated name, 4 m's will give the full name)

Alternatively, you could put:

=A1

in an adjacent cell, format this as mm yyyy (or one of the others),
and copy down.

Another alternative still is to use this formula:

=TEXT(A1,"mm yyyy")

and copy down, although this will return a text result.

Hope this helps.

Pete

On Mar 13, 3:47 pm, J.Mart wrote:
Hi,

I am trying to use a sumproduct function to return how many M&A deals were
over a billion dollars in a given year. I've been trying
=SUMPRODUCT(($A$18:$A$3295=$C4)*($D$18:$D$3295=10 00))
C4-represents a year. The sheet is set up as follows:
Output
* * * * * * * No. of deals 1billion plus * * *
* * * 2002
* * * 2003

DATA
Col.A * * * *Col.D
YEAR * * * *Value in $Millions

Second question: If I have a list of dates m/d/yy how can I write an
equation that will just return the month and year?

Thanks!- Hide quoted text -

- Show quoted text -