SUMIF every 2nd cell

[Guest]

Hi,
I hope someone can give me a hand here.

I have the following:

D E F G H I J K
5 10 601 8 601 3 614 4 601

I want to program a cell that will add up D5, F5, H5, J5 only if text in E5,
G5, I5 and K5 is "601", so that my result should be 22

Thanks for your assistance

Markus

 

[PCLIVE]

If "601" really is text, then:
=IF(AND(E5="601",G5="601",I5="601",K5="601"),SUM(D5,F5,H5,J5))

But if it is a number, then:
=IF(SUM(E5,G5,I5,K5)=2404,SUM(D5,F5,H5,J5))

You'll need to address if the result is False. Right now this formula just
returns False.

HTH,
Paul

 

[Guest]

Markus,

I'm not sure if you can accomplish what you want in one cell using SUMIF.
Give this a try though:
Put the formula below in D6 and then fill across under the rest of your
data.
=MOD(COLUMN(D5),2)
Then in D7 put your SUMIF formula.
=SUMIF($D$6:$K$6,0,$D$5:$K$5)

Hope this helps.

 

[Guest]

Markus

You can use an array expression (which means, after entering the formula
below, you need to hit CTL-SHFT-ENTER instead of just ENTER).

=SUM(D5*(E5="601"), F5*(G5="601"),H5*(I5="601"),J5*(K5="601"))

If you do it right, the "{}" brackets will appear around the expression in
the edit window at the top when this cell is selected.

Will

 

[Guest]

Thanks Paul...pardon me but I don't know how to complete this with how to
address if the result is false...if I don't put nothing, I still get a "false
" cell even though the corresponding cells do have what it takes to return a
right calculation.

Also, the comma (",") doesn't seem to do great on my program, i.e. the cell
squared colors are not coming up, but when I use ";", the do come up in
colors giving the impression something will happen...any clue ?

Thanks

 

[Guest]

Thanks Will,

I didn't get much success with the commas (",") between the brackets. The
colored square cells didn't come up while they did using ";"...I followed
your instructions except for using ";" otherwise it returned an error. But
event with the semi colon, I get 0 even if I have data in the right cells.

Any ideas ?

Thanks for your help.

 

[Guest]

Markus

Strange. That exact formula works fine for me. Putting in the semicolons
screws it up. Incidentally, I mispoke about this having to be an array
expression. It's unnecessary in this situation, so you don't need to use the
CTL-SHFT-ENTER. What version of Excel are you using? I have 2003.

Something else you might try is to just use simple addition instead of the
SUM function:

=D5*(E5="601")+F5*(G5="601")+H5*(I5="601")+J5*(K5="601")

Will

 

[Guest]

Will,

You are a genius !! It worked with that one...thanks a million for your
promp assistance...

Can I poke you with something else if the need arise ??

Sincerely,
Markus

 

[Guest]

As much as I'd like to be poked to help every now and then, I only
occasionally visit this site (for tips and to help if I can) and doubt that
my employer would appreciate my giving out my email for this purpose. But
fear not - there are many people more knowledgeable than I who seem to
monitor this site regularly. If you submit a question and include enough
detail for it to be fully understood, you'll get a solution (if there is one).
Will