| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif Question?
I'm trying to figure out how to add a columns of numbers if there is a number
in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Hi jay
try to use: =sumproduct((isnumber($Y$2:$Y$268))*($z$2:$z$268)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Maybe this:
=SUMPRODUCT(--(ISNUMBER(Y2:Y268)),--(ISNUMBER(Z2:Z268)),Y2:Y268) HTH, Paul. -- "Jay" wrote in message ... I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Marcelo, That worked, I just has to flip the formula (y and z). I hate to
ask but can you explain $ symbols role in the formula. That's a new one for me? "Marcelo" wrote: Hi jay try to use: =sumproduct((isnumber($Y$2:$Y$268))*($z$2:$z$268)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Paul,
That worked as well. Thanks. What role does the -- play in the formula? "PCLIVE" wrote: Maybe this: =SUMPRODUCT(--(ISNUMBER(Y2:Y268)),--(ISNUMBER(Z2:Z268)),Y2:Y268) HTH, Paul. -- "Jay" wrote in message ... I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
it ($) is just to freeze in case of copy the formula
if you have in A1 =b1, and you copy it to a2, excel change to =b2, the $ freeze the column or the row you can have =b1 =$b1 =b$1 =$B$1 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: Marcelo, That worked, I just has to flip the formula (y and z). I hate to ask but can you explain $ symbols role in the formula. That's a new one for me? "Marcelo" wrote: Hi jay try to use: =sumproduct((isnumber($Y$2:$Y$268))*($z$2:$z$268)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
The "--" coverts the items to a number, either 1 or 0. So for each
occurrence of items that are numbers, these will be registered as 1's. The formula could also be written as: =SUMPRODUCT((ISNUMBER(Y2:Y268))*(ISNUMBER(Z2:Z268) ),Y2:Y268) -- "Jay" wrote in message ... Paul, That worked as well. Thanks. What role does the -- play in the formula? "PCLIVE" wrote: Maybe this: =SUMPRODUCT(--(ISNUMBER(Y2:Y268)),--(ISNUMBER(Z2:Z268)),Y2:Y268) HTH, Paul. -- "Jay" wrote in message ... I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Thanks!!!
"Marcelo" wrote: it ($) is just to freeze in case of copy the formula if you have in A1 =b1, and you copy it to a2, excel change to =b2, the $ freeze the column or the row you can have =b1 =$b1 =b$1 =$B$1 hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: Marcelo, That worked, I just has to flip the formula (y and z). I hate to ask but can you explain $ symbols role in the formula. That's a new one for me? "Marcelo" wrote: Hi jay try to use: =sumproduct((isnumber($Y$2:$Y$268))*($z$2:$z$268)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Jay" escreveu: I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumif Question?
Thanks!!!
"PCLIVE" wrote: The "--" coverts the items to a number, either 1 or 0. So for each occurrence of items that are numbers, these will be registered as 1's. The formula could also be written as: =SUMPRODUCT((ISNUMBER(Y2:Y268))*(ISNUMBER(Z2:Z268) ),Y2:Y268) -- "Jay" wrote in message ... Paul, That worked as well. Thanks. What role does the -- play in the formula? "PCLIVE" wrote: Maybe this: =SUMPRODUCT(--(ISNUMBER(Y2:Y268)),--(ISNUMBER(Z2:Z268)),Y2:Y268) HTH, Paul. -- "Jay" wrote in message ... I'm trying to figure out how to add a columns of numbers if there is a number in an adjacent column. So for example if cell Y245 and Z245 both contain numbers then Y245 would be added to the sum but if if Z245 did not contain a number then Y245 would not be counted. I'm thinking this is a SUMIF calculation but am unsure of how to input the data properly. My Ranges are Y2:Y268 and Z2:Z68. Any help on this would be greatly appreciated. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif question | Excel Discussion (Misc queries) | |||
| Sumif question - I think? | Excel Worksheet Functions | |||
| SUMIF Question | Excel Worksheet Functions | |||
| Sumif question | Excel Worksheet Functions | |||
| SUMIF Question | Excel Discussion (Misc queries) | |||
Linear Mode
