sumif

G

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
 
G

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)

---
 
G

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)

---
 
G

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
 
R

Roger Govier

Hi

Try amending Max's formula to

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

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"
 
G

Guest

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

thanks :)

thanks community
 
G

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


Max said:
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"

---
 
R

Roger Govier

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

--
Regards

Roger Govier


Max said:
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"

---
 
M

Max

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

Max

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top