Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
what fomula would I use to add criteria in one cell only when the cell next
to it has data entered? Basically, I don't want the first cell to be part of a formula until it has relevency. The cells are not part of a continuous range either(i.e. they might be a4, a14,a24,a34 etc.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Maybe you mean something like this in say, B2, copied down:
=if(A2="","",SUMIF(€¦)) The front IF check on A2: =if(A2="","", ... will avert further evaluation of the SUMIF should A2 be either blank or contain a zero length null string: "" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote: what fomula would I use to add criteria in one cell only when the cell next to it has data entered? Basically, I don't want the first cell to be part of a formula until it has relevency. The cells are not part of a continuous range either(i.e. they might be a4, a14,a24,a34 etc.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Maybe you mean something like this in say, B2, copied down:
=if(A2="","",SUMIF(€¦)) The front IF check on A2: =if(A2="","", ... will avert further evaluation of the SUMIF should A2 be either blank or contain a zero length null string: "" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote: what fomula would I use to add criteria in one cell only when the cell next to it has data entered? Basically, I don't want the first cell to be part of a formula until it has relevency. The cells are not part of a continuous range either(i.e. they might be a4, a14,a24,a34 etc.) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Can I do sumif when the range is not continuous?? example
=sumif(a4,a24,a34,a44,"0",(b4,b24,b34,b44)) "Max" wrote: Maybe you mean something like this in say, B2, copied down: =if(A2="","",SUMIF(€¦)) The front IF check on A2: =if(A2="","", ... will avert further evaluation of the SUMIF should A2 be either blank or contain a zero length null string: "" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote: what fomula would I use to add criteria in one cell only when the cell next to it has data entered? Basically, I don't want the first cell to be part of a formula until it has relevency. The cells are not part of a continuous range either(i.e. they might be a4, a14,a24,a34 etc.) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
"eric" wrote:
Can I do sumif when the range is not continuous?? example =sumif(a4,a24,a34,a44,"0",(b4,b24,b34,b44)) Don't think so. I suppose you have your reasons why you can't use the entire range A4:A44. One quick workaround. In an empty col to the right, say col E, do a one-time manual flagging by inputting say: x into the corresponding cells: e4,e24,e34,e44. Then you could point the SUMIF to col E, and use: =SUMIF(E2:E44,"x",B2:B44) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Thanks for helping. I hope I'm not taking advantage of your help but could I
send you my spread sheet and show you what I am trying to accomplish? I know there has to be an easier way in excel to do what I want(I'm not trying to get that complicated) but I'm not explaining it well. Eric "Max" wrote: "eric" wrote: Can I do sumif when the range is not continuous?? example =sumif(a4,a24,a34,a44,"0",(b4,b24,b34,b44)) Don't think so. I suppose you have your reasons why you can't use the entire range A4:A44. One quick workaround. In an empty col to the right, say col E, do a one-time manual flagging by inputting say: x into the corresponding cells: e4,e24,e34,e44. Then you could point the SUMIF to col E, and use: =SUMIF(E2:E44,"x",B2:B44) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Use either of the 2 free filehosts below to upload a sample of your actuals
(Desensitize it first, as needed). Then copy n paste the generated link to your sample file in response he http://www.flypicture.com/ http://cjoint.com/index.php -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote in message ... Thanks for helping. I hope I'm not taking advantage of your help but could I send you my spread sheet and show you what I am trying to accomplish? I know there has to be an easier way in excel to do what I want(I'm not trying to get that complicated) but I'm not explaining it well. Eric |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Max, thanks again for your help. I attached a sample of my file in the link
below. In cell F2 I put a note about what I am trying to accomplish. http://www.flypicture.com/download/MzAwMDk= "Max" wrote: Use either of the 2 free filehosts below to upload a sample of your actuals (Desensitize it first, as needed). Then copy n paste the generated link to your sample file in response he http://www.flypicture.com/ http://cjoint.com/index.php -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote in message ... Thanks for helping. I hope I'm not taking advantage of your help but could I send you my spread sheet and show you what I am trying to accomplish? I know there has to be an easier way in excel to do what I want(I'm not trying to get that complicated) but I'm not explaining it well. Eric |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
the other one is not working for me
try this http://www.flypicture.com/download/MzAwMjA= "Max" wrote: Use either of the 2 free filehosts below to upload a sample of your actuals (Desensitize it first, as needed). Then copy n paste the generated link to your sample file in response he http://www.flypicture.com/ http://cjoint.com/index.php -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "eric" wrote in message ... Thanks for helping. I hope I'm not taking advantage of your help but could I send you my spread sheet and show you what I am trying to accomplish? I know there has to be an easier way in excel to do what I want(I'm not trying to get that complicated) but I'm not explaining it well. Eric |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
Put in F2:
=IF($B2="","",SUMPRODUCT((E$24:E$436<"")*($B$24:$ B$436=$B2),F$24:F$436)) Copy down to F19. That should do it for Block 1. To propagate, just copy F2:F19 and paste onto I2:I19, L2:L19, etc -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif
A slight correction for the upper range limit
(should be row 448 based on the last behaviour 7): Put instead in F2: =IF($B2="","",SUMPRODUCT((E$24:E$448<"")*($B$24:$ B$448=$B2),F$24:F$448)) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF | New Users to Excel | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |