sumif

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Can I do sumif when the range is not continuous?? example
=sumif(a4,a24,a34,a44,">0",(b4,b24,b34,b44))

 

[Guest]

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)

---

 

[Guest]

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]

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

http://www.flypicture.com/
http://cjoint.com/index.php

 

[Guest]

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=

 

[Guest]

the other one is not working for me
try this

http://www.flypicture.com/download/MzAwMjA=

 

[Guest]

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

---

 

[Guest]

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

---