If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
adding figures that match 2 true and 1 false if statement conditio
As ever many thanks in advance for any help.
I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
#2
|
|||
|
|||
adding figures that match 2 true and 1 false if statement conditio
Perhaps something like this:
=SUMPRODUCT((A2:A10="sid")*(B2:B10="car")*(E2:E10 "Rejected")*(C2:C10)) explained: =SUMPRODUCT(LogicCheck1 * LogicCheck2 * LogicCheck3 * ValuesToSum) You're example was a little unclear, as it doesn't clearly say how the 80 is calculated, and column B has values, not text. Hopefully you can adapt this formula's pattern to suit. -- Best Regards, Luke M "UKMAN" wrote in message ... As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
#3
|
|||
|
|||
adding figures that match 2 true and 1 false if statement conditio
Hi,
As this is a consolidation I assume you have in column A name (sid in A2), in column B car in B2, in column c you want the total. I assume that the information is in sheet called "Data", change it and range to fit your needs. =sumproduct(--(A1=Data!$A$2:$A$1000),--(Data!$E$2:$E$1000"Rejected"),Data!$$B$2:$B$1000 ) "UKMAN" wrote: As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
#4
|
|||
|
|||
adding figures that match 2 true and 1 false if statement conditio
Also, since you have a nice table format, you could prb create the summary
table via PivotTable. This would save on calc time and flexibility. -- Best Regards, Luke M "Luke M" wrote in message ... Perhaps something like this: =SUMPRODUCT((A2:A10="sid")*(B2:B10="car")*(E2:E10 "Rejected")*(C2:C10)) explained: =SUMPRODUCT(LogicCheck1 * LogicCheck2 * LogicCheck3 * ValuesToSum) You're example was a little unclear, as it doesn't clearly say how the 80 is calculated, and column B has values, not text. Hopefully you can adapt this formula's pattern to suit. -- Best Regards, Luke M "UKMAN" wrote in message ... As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
#5
|
|||
|
|||
adding figures that match 2 true and 1 false if statement cond
Eduardo,
if only I could get a course I would understand some of your code but I have been able to amend it to meet my needs so your are brill many thanks UKMAN "Eduardo" wrote: Hi, As this is a consolidation I assume you have in column A name (sid in A2), in column B car in B2, in column c you want the total. I assume that the information is in sheet called "Data", change it and range to fit your needs. =sumproduct(--(A1=Data!$A$2:$A$1000),--(Data!$E$2:$E$1000"Rejected"),Data!$$B$2:$B$1000 ) "UKMAN" wrote: As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
#6
|
|||
|
|||
adding figures that match 2 true and 1 false if statement cond
Hi,
take a look at this web it has good explanations and examples using sumproduct http://www.xldynamic.com/source/xld.SUMPRODUCT.html thanks for the feedback "UKMAN" wrote: Eduardo, if only I could get a course I would understand some of your code but I have been able to amend it to meet my needs so your are brill many thanks UKMAN "Eduardo" wrote: Hi, As this is a consolidation I assume you have in column A name (sid in A2), in column B car in B2, in column c you want the total. I assume that the information is in sheet called "Data", change it and range to fit your needs. =sumproduct(--(A1=Data!$A$2:$A$1000),--(Data!$E$2:$E$1000"Rejected"),Data!$$B$2:$B$1000 ) "UKMAN" wrote: As ever many thanks in advance for any help. I am struggling to understand how I can say not equal in an if statement in excel I need to consildate a table which layout is basically: Col A Col b Col c Col d Col e (name Car basic Bonus status) sid 50 100 10 On hold mary 20 50 5 approved sid 40 90 10 rejected sid 30 35 10 review As this is to do with a budget the consolidation will use the same headings but the status I want to exclude from the calc for each col is "rejected". therefore the summary field need to say if Col a = sid, if col b = Car, if col e not equal "Rejected" then the total car costs for sid is 80. (hope that makes sence As i mentioned this is a cutdown version of a large sheet. Cheers UKMAN |
Thread Tools | |
Display Modes | |
|
|