Counting unique items...please help
 

I would like to create a formula that would add up a range of cells in column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

If by 'value' you mean a number, then

=SUMPRODUCT(--ISNUMBER(Q1:Q7),B1:B7)

If you mean a non-blank cell, then

=SUMPRODUCT(--NOT(ISBLANK(Q1:Q7)),B1:B7)


"Lisa" wrote:

I would like to create a formula that would add up a range of cells in column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

Hi Lisa,

=SUMIF(A1:A5,"a",B1:B5)
=SUMIF(A1:A5,C1,B1:B5)

Sums the value in Col. B if Col. A has an "a", no quotes, or you can refer
to a cell like in the second formula.

HTH
Regards,
Howard

"Lisa" wrote in message
...
I would like to create a formula that would add up a range of cells in
column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

I mean a non-blank cell. In a new worksheet I am summarizing data from
corresponding worksheets. I tried the second formula you suggested, but I
think it is multiplying the values b/c I get an outrageously high number.
Should I be using something different then SUMPRODUCT???

"Duke Carey" wrote:

If by 'value' you mean a number, then

=SUMPRODUCT(--ISNUMBER(Q1:Q7),B1:B7)

If you mean a non-blank cell, then

=SUMPRODUCT(--NOT(ISBLANK(Q1:Q7)),B1:B7)


"Lisa" wrote:

I would like to create a formula that would add up a range of cells in column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

Hi Howard...I'm feeling really stupid this morning, but I'm not really
following your formula. What I would like to do is add the sum of the values
in column B only if there is a blank cell in column Q.
Thanks for the tips.

"L. Howard Kittle" wrote:

Hi Lisa,

=SUMIF(A1:A5,"a",B1:B5)
=SUMIF(A1:A5,C1,B1:B5)

Sums the value in Col. B if Col. A has an "a", no quotes, or you can refer
to a cell like in the second formula.

HTH
Regards,
Howard

"Lisa" wrote in message
...
I would like to create a formula that would add up a range of cells in
column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

NEVER MIND Duke - made a silly mistake - formula seems to be working great.
Thank you so much for the help!

"Lisa" wrote:

I mean a non-blank cell. In a new worksheet I am summarizing data from
corresponding worksheets. I tried the second formula you suggested, but I
think it is multiplying the values b/c I get an outrageously high number.
Should I be using something different then SUMPRODUCT???

"Duke Carey" wrote:

If by 'value' you mean a number, then

=SUMPRODUCT(--ISNUMBER(Q1:Q7),B1:B7)

If you mean a non-blank cell, then

=SUMPRODUCT(--NOT(ISBLANK(Q1:Q7)),B1:B7)


"Lisa" wrote:

I would like to create a formula that would add up a range of cells in column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

ACTUALLY, it's not working. I tried the following:

=SUM(--NOT(ISBLANK(Q8:q300)),F8:F300)

and the formula is calculating the sum of column F but disregarding the
condition. IOW, it's ignoring if it's a blank cell or not. Sugesstions??

"Lisa" wrote:

NEVER MIND Duke - made a silly mistake - formula seems to be working great.
Thank you so much for the help!

"Lisa" wrote:

I mean a non-blank cell. In a new worksheet I am summarizing data from
corresponding worksheets. I tried the second formula you suggested, but I
think it is multiplying the values b/c I get an outrageously high number.
Should I be using something different then SUMPRODUCT???

"Duke Carey" wrote:

If by 'value' you mean a number, then

=SUMPRODUCT(--ISNUMBER(Q1:Q7),B1:B7)

If you mean a non-blank cell, then

=SUMPRODUCT(--NOT(ISBLANK(Q1:Q7)),B1:B7)


"Lisa" wrote:

I would like to create a formula that would add up a range of cells in column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

=SUMPRODUCT(--NOT(ISBLANK(Q8:q300)),F8:F300)

or

=SUM(--NOT(ISBLANK(Q8:q300))*F8:F300)

In the second case you needed to multiply. Also the second formula
needs to be array-entered (Ctrl+Shift+Enter)

HTH
Kostis Vezerides

On Mar 1, 5:21 pm, Lisa wrote:
ACTUALLY, it's not working. I tried the following:

=SUM(--NOT(ISBLANK(Q8:q300)),F8:F300)

and the formula is calculating the sum of column F but disregarding the
condition. IOW, it's ignoring if it's a blank cell or not. Sugesstions??

Counting unique items...please help
 

Hi Lisa,

Column Q... blank or not blank???

<I would like to create a formula that would add up a range of cells in
column
<B only if there is a value entered in Column Q.

=SUM(--NOT(ISBLANK(Q1:Q7))*B1:B7)

<What I would like to do is add the sum of the values
<in column B only if there is a blank cell in column Q.

=SUM(--ISBLANK(Q1:Q7))*B1:B7

HTH
Regards,
Howard

"Lisa" wrote in message
...
I would like to create a formula that would add up a range of cells in
column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???

Counting unique items...please help
 

Forgot to say, use Ctrl+Shift+Enter on these formulas

Howard

"L. Howard Kittle" wrote in message
. ..
Hi Lisa,

Column Q... blank or not blank???

<I would like to create a formula that would add up a range of cells in
column
<B only if there is a value entered in Column Q.

=SUM(--NOT(ISBLANK(Q1:Q7))*B1:B7)

<What I would like to do is add the sum of the values
<in column B only if there is a blank cell in column Q.

=SUM(--ISBLANK(Q1:Q7))*B1:B7

HTH
Regards,
Howard

"Lisa" wrote in message
...
I would like to create a formula that would add up a range of cells in
column
B only if there is a value entered in Column Q. I've tried a couple of
things to no avail. Any suggestions???