sumproduct in a range
 

I have 27 columns. The first column refers to a calendar year ie 2003, 2004.
There are multilple occurences of the same year in this column. The rest of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to the
year selected.

I'm not too sure how to use the sumproduct function but this is the formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

One way

=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

using excel built in method of (--(range=x),--(range=y)

will not work if the ranges have different sizes

--
Regards,

Peo Sjoblom

(No private emails please)


"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

try this idea


=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

--
Don Guillett
SalesAid Software

"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

Hi

=SUM(IF($A$12:$A$24=W34,$B$12:$BA$24,0))
entered as an array formula (Ctrl+Shift+Enter)


Arvi Laanemets


"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

Tat wrote:
I have 27 columns. The first column refers to a calendar year ie 2003, 2004.
There are multilple occurences of the same year in this column. The rest of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to the
year selected.

I'm not too sure how to use the sumproduct function but this is the formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

You have to switch to the comma syntax to the "star" syntax:

=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

if B12:BA24 does not house any text values.

Otherwise, one of:

=SUM(IF(A12:A24=W34,IF(B12:BA240,1,0)))

=SUM(IF(A12:A24=W34,IF(ISNUMBER(B12:BA24)*(B12:BA2 40),1,0)))

which must be confirmed with control+shift+enter instead of just enter.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Tried it but didn't work.
Used =Sum(IF(a12:a24=w34,if(b12:ba240,1,0))) that A.Akyurek suggested with
cntrl +shift+enter. That worked.

Thanks for your efforts.

"Don Guillett" wrote:

try this idea


=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

--
Don Guillett
SalesAid Software

"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

Tried it but didn't work.
Used =Sum(IF(a12:a24=w34,if(b12:ba240,1,0))) that A.Akyurek suggested with
cntrl +shift+enter. That worked.

Thanks for your efforts.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

using excel built in method of (--(range=x),--(range=y)

will not work if the ranges have different sizes

--
Regards,

Peo Sjoblom

(No private emails please)


"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

Thank you. That works great!

"Arvi Laanemets" wrote:

Hi

=SUM(IF($A$12:$A$24=W34,$B$12:$BA$24,0))
entered as an array formula (Ctrl+Shift+Enter)


Arvi Laanemets


"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

Thanks for your suggestion with conditional sum if. That worked. (sumproduct
did not).

Have a good one!

"Aladin Akyurek" wrote:

Tat wrote:
I have 27 columns. The first column refers to a calendar year ie 2003, 2004.
There are multilple occurences of the same year in this column. The rest of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to the
year selected.

I'm not too sure how to use the sumproduct function but this is the formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.

You have to switch to the comma syntax to the "star" syntax:

=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

if B12:BA24 does not house any text values.

Otherwise, one of:

=SUM(IF(A12:A24=W34,IF(B12:BA240,1,0)))

=SUM(IF(A12:A24=W34,IF(ISNUMBER(B12:BA24)*(B12:BA2 40),1,0)))

which must be confirmed with control+shift+enter instead of just enter.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

tested and worked fine.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this idea


=SUMPRODUCT((A12:A24=W34)*(B12:BA240))

--
Don Guillett
SalesAid Software

"Tat" wrote in message
...
I have 27 columns. The first column refers to a calendar year ie 2003,
2004.
There are multilple occurences of the same year in this column. The rest
of
the columns may or may not have numbers in the cells.

I would like to count the cells that do have numbers that correspond to
the
year selected.

I'm not too sure how to use the sumproduct function but this is the
formula
I created and doesn't work:
=SUMPRODUCT(--(A12:A24=W34),--(B12:BA240))

W34 indicates the year I would like the used cells counted.
Assistance is mighty appreciated.