sumif

[Guest]

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

 

[Guest]

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)

---

 

[Guest]

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)

---

 

[Guest]

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

 

[Roger Govier]

Hi

Try amending Max's formula to

=SUMPRODUCT((B1:B10="CHQ")*(A1:A10={"","QCDM","MER"})*E1:E10)

 

[Max]

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"

 

[Guest]

thanks Roger Govier that's great
ur formula works but
short of the last sumproduct ?

thanks

thanks community

 

[Guest]

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

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"

---

 

[Roger Govier]

Sorry both.
Had not read the thread carefully enough and didn't notice the other
criteria.

--
Regards

Roger Govier

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]

Welcome, good to hear that. It was a combined effort with Roger's inputs
which paved the path here <g>

 

[Max]

Thought your earlier input to the OP's follow-through Q was key.
I just did the simple follow-through <g>