Thread: sum values based off matches from two ranges

1. sum values based off matches from two ranges

Hi Guru's,

I have a set of data with multiple ranges which contain matches. Below is an example data set:

 Header 1 Header 2 Header 3 Header 4 a 1 a 3 c 3 e 2 b 1 c 5 z 4 h 1

Basically I would like to return the sum value of column header 4 for all matching/duplicated letters under Header 1 and Header 3. In this example there are the letters "a" & "c" under both header 1 and header 2. Therefore, the sum value returned should be 8 (3+5).

Thank you for your time Guru's.

2. Originally Posted by Bl4ckPantha
return the sum value of column header 4 for all matching/duplicated letters under Header 1 and Header 3.
This is your eighth post, meaning you know it is desirable to set an example of a workbook.
Try this formula below and for other example formulas see tutorial 'Sum all values for multiple criteria from one column'.
Code:
`=SUM(SUMIF(C2:C5;{"A";"C"};D2:D5))`
I hope it helps

3. Hi Navic,

Thanks for your reply. Unfortunately this formula does not work. I have also attached a workbook this time.

The formula needs to be able to sum all matching/duplicate values in column A & C, using column D as the sum range and I'd also like it to ignore any matches if the value in column B is "0" (Zero). The data set I am working with has thousands of unique and matching values between both columns, so I cannot specify which values to sum (e.g. "A" & "C")..

In the attached I have highlighted the matches based on column B being greater than 0. So all values highlighted in column D should be summed.

Guru Example.xlsx

4. Why not SURF TOWNSVILLE?

Is this any use?

=SUMPRODUCT(\$D\$2:\$D\$9*(\$C\$2:\$C\$9=\$A\$2:\$A\$9))

Ort this:

=SUMPRODUCT(IF(\$B\$2:\$B\$9>0,(\$C\$2:\$C\$9=\$A\$2:\$A\$9)*\$D\$2:\$D\$9))

... array entered using CTRL+SHIFT+ENTER.

5. Originally Posted by Bl4ckPantha
Hi Navic, Thanks for your reply. Unfortunately this formula does not work.
Hello,
please indicate what does not work when you get an answer. It is very frustrating for someone spending his/her free time to see his/her work was not the required solution without knowing what was wrong. All members also try to learn from their mistakes ( if any)

In this case, did you replace the semi colons in Navic's formula with commas ( see his signature) ?

6. Originally Posted by Bl4ckPantha
Unfortunately this formula does not work. I have also attached a workbook this time.
This is something else, much more detailed explanation and expected result with the example Workbook.

If you've studied my tutorial on link, then you could have come to a solution yourself.
Look at my example in the attachment.
I used a formula from tutorial (please see hidden helper columns)
Code:
`=SUMPRODUCT(D2:D9;--ISNUMBER(MATCH(C2:C9;H2:H9;0)))`
Regards

7. Hi Navic,

Thank you so much for your assistance, these formula's provide my exact requirement. I'll be sure to be much clearer next time I post, sorry for wasting your time earlier.

Regards

8. Sum(if...)

Hi guys,

I wrote this formula =SUM(IF(COUNTIF(A2:A9;C2:C9)=1;D29);0)-SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)=0;D29;0);0)) that should work correctly too. no helper column is needed.
you can try it. I added this formula =AND(COUNTIF(\$A\$2:\$A\$9;\$C2)=1;SUMIF(\$A\$2:\$A\$9;C2;\$B\$2:\$B\$9)<>0) to condititional formatting , so you can see, which values the total value consist of.

Andrej

9. Originally Posted by ajko7
I wrote this formula ............... no helper column is needed.
Great formula

10. Originally Posted by navic
Great formula
thanks :-).

I tried it in one formula without the minus part (with if and, or more if), but it din't work. I made something wrong.
this shortly version should be usefull too: =SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)>0;D29;0);0))

Andrej