ExcelBanter - sumif

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - sumif (https://www.excelbanter.com/excel-discussion-misc-queries/157791-sumif.html)

Eric

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.)

Max

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.)

Max

sumif

Eric

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.)

Max

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
---

Eric

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
---

Max

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

Eric

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

Eric

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

Max

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
---

Max

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
---

All times are GMT +1. The time now is 10:39 PM.