| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF Not | Excel Discussion (Misc queries) | |||
| SUMIF Help | Excel Discussion (Misc queries) | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
