| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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??? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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?? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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??? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locate unique items | Excel Worksheet Functions | |||
| counting unique items(values or text) | Excel Worksheet Functions | |||
| Sumif only unique items | Excel Discussion (Misc queries) | |||
| Picking unique Items | Excel Discussion (Misc queries) | |||
| counting unique items | Excel Discussion (Misc queries) | |||
Linear Mode
