Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
If "601" really is text, then:
=IF(AND(E5="601",G5="601",I5="601",K5="601"),SUM(D 5,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 "Markus" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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. "Markus" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 "Markus" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 "PCLIVE" wrote: If "601" really is text, then: =IF(AND(E5="601",G5="601",I5="601",K5="601"),SUM(D 5,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 "Markus" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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. "roadkill" wrote: 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 "Markus" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 "Markus" wrote: 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. "roadkill" wrote: 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 "Markus" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 "roadkill" wrote: 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 "Markus" wrote: 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. "roadkill" wrote: 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 "Markus" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMIF every 2nd cell
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 "Markus" wrote: 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 "roadkill" wrote: 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 "Markus" wrote: 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. "roadkill" wrote: 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 "Markus" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif based on value in cell | Excel Worksheet Functions | |||
Contains a Value Cell and Sumif? | Excel Worksheet Functions | |||
SUMIF with a reference to a cell | Excel Discussion (Misc queries) | |||
SUMIF cell = "Yes" | Excel Discussion (Misc queries) | |||
SUMIF, wildcard and cell ref | Excel Worksheet Functions |