Two questions-SUMPRODUCT & IF something

[J.Mart]

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>=1000))
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!

 

[Pete_UK]

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

 

[Dave Peterson]

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]

Dave- thank you. This was helpful.

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]

Thanks!

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

 

[Pete_UK]

You're welcome.

Pete

Thanks!











- Show quoted text -