|
sumif
hi community
can community assist me to solve this..... A B C E 1 ITTCHG ADV 5.00 2 MER ADV 3.00 3 TRFCPU ADV 5.00 4 MER ADV 5.00 5 BILL ATP 12.00 6 CHQ 5.00 7 QCDM CHQ 6.00 8 CHQ 5.00 9 QCDM CHQ 2.00 10 21334 CHQ 10.00 what i want to do with the above range are these format: e.g 1 If Col B = "CHQ" & Col = "" sum(E?:E?) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) and in this e.g 2 if Col = "CHQ" sum(e.g1'sum' + e.g2'sum') e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) can the above to done ? thanks community :) oldLearner57 |
sumif
One way is to use sumproduct for the multi-criteria summing
These 3 should give you a good start .. e.g 1 If Col B = "CHQ" & Col A = "" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10=""),C1:C10) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10="QCDM"),C1:C10) e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) =SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),C1:C10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "oldLearner57" wrote: hi community can community assist me to solve this..... A B C E 1 ITTCHG ADV 5.00 2 MER ADV 3.00 3 TRFCPU ADV 5.00 4 MER ADV 5.00 5 BILL ATP 12.00 6 CHQ 5.00 7 QCDM CHQ 6.00 8 CHQ 5.00 9 QCDM CHQ 2.00 10 21334 CHQ 10.00 what i want to do with the above range are these format: e.g 1 If Col B = "CHQ" & Col = "" sum(E?:E?) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) and in this e.g 2 if Col = "CHQ" sum(e.g1'sum' + e.g2'sum') e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) can the above to done ? thanks community :) oldLearner57 |
sumif
Oops, C1:C10 in the earlier sumproducts should have read as E1:E10, ie
e.g 1 If Col B = "CHQ" & Col A = "" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10=""),E1:E10) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10="QCDM"),E1:E10) e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) =SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
sumif
thanks Max, that really helps :)
and is there possible for me to combined all the three formulae into 1 so as I can use these 3 formula as 1.. (if can, how do i do that??) hope u understand what I am trying to say....?? :) thanks again for the help very appreciated thanks community as well -- oldLearner57 "Max" wrote: Oops, C1:C10 in the earlier sumproducts should have read as E1:E10, ie e.g 1 If Col B = "CHQ" & Col A = "" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10=""),E1:E10) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10="QCDM"),E1:E10) e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) =SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
sumif
Hi
Try amending Max's formula to =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER "})*E1:E10) -- Regards Roger Govier "oldLearner57" wrote in message ... thanks Max, that really helps :) and is there possible for me to combined all the three formulae into 1 so as I can use these 3 formula as 1.. (if can, how do i do that??) hope u understand what I am trying to say....?? :) thanks again for the help very appreciated thanks community as well -- oldLearner57 "Max" wrote: Oops, C1:C10 in the earlier sumproducts should have read as E1:E10, ie e.g 1 If Col B = "CHQ" & Col A = "" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10=""),E1:E10) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10="QCDM"),E1:E10) e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) =SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
sumif
Roger, thanks for the help.
Thought it might be something like: =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM"})*E1 :E10) +SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) since the OP's other value within B1:B10 is "ADV" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Hi Try amending Max's formula to =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER "})*E1:E10) -- Regards Roger Govier |
sumif
thanks Roger Govier that's great
ur formula works but short of the last sumproduct ? thanks :) thanks community -- oldLearner57 "Roger Govier" wrote: Hi Try amending Max's formula to =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER "})*E1:E10) -- Regards Roger Govier "oldLearner57" wrote in message ... thanks Max, that really helps :) and is there possible for me to combined all the three formulae into 1 so as I can use these 3 formula as 1.. (if can, how do i do that??) hope u understand what I am trying to say....?? :) thanks again for the help very appreciated thanks community as well -- oldLearner57 "Max" wrote: Oops, C1:C10 in the earlier sumproducts should have read as E1:E10, ie e.g 1 If Col B = "CHQ" & Col A = "" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10=""),E1:E10) e.g 2 If Col B = "CHQ" & Col A = "QCDM" sum(E?:E?) =SUMPRODUCT((B1:B10="CHQ")*(A1:A10="QCDM"),E1:E10) e.g 3 If Col B = "ADV" & Col A = "MER" sum(E?:E?) =SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
sumif
hi Max
10s again, now I got all the 3 sumproduct formula and combined into 1 test :) very much appreciated :) thanks community as well :) -- oldLearner57 "Max" wrote: Roger, thanks for the help. Thought it might be something like: =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM"})*E1 :E10) +SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) since the OP's other value within B1:B10 is "ADV" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Hi Try amending Max's formula to =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER "})*E1:E10) -- Regards Roger Govier |
sumif
Sorry both.
Had not read the thread carefully enough and didn't notice the other criteria. -- Regards Roger Govier "Max" wrote in message ... Roger, thanks for the help. Thought it might be something like: =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM"})*E1 :E10) +SUMPRODUCT((B1:B10="ADV")*(A1:A10="MER"),E1:E10) since the OP's other value within B1:B10 is "ADV" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Hi Try amending Max's formula to =SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER "})*E1:E10) -- Regards Roger Govier |
sumif
Welcome, good to hear that. It was a combined effort with Roger's inputs
which paved the path here <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "oldLearner57" wrote in message ... hi Max 10s again, now I got all the 3 sumproduct formula and combined into 1 test :) very much appreciated :) |
sumif
Thought your earlier input to the OP's follow-through Q was key.
I just did the simple follow-through <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Sorry both. Had not read the thread carefully enough and didn't notice the other criteria. -- Regards Roger Govier |
| All times are GMT +1. The time now is 08:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com