sum values based off matches from two ranges

Bl4ckPantha

New member
Joined
May 23, 2018
Messages
12
Reaction score
0
Points
0
Excel Version(s)
2016
Hi Guru's,

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

Header 1Header 2Header 3Header 4
a1a3
c3e2
b1c5
z4h1

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.
 
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.

View attachment Guru Example.xlsx
 
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.
 
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) ?
 
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
 

Attachments

  • Bl4ckPantha-9548-navicExample.xlsx
    10.8 KB · Views: 15
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
 
Sum(if...)

Hi guys,


I wrote this formula =SUM(IF(COUNTIF(A2:A9;C2:C9)=1;D2:D9);0)-SUM(IF(COUNTIF(A2:A9;C2:C9)=1;IF(SUMIF(A2:A9;C2:C9;B2:B9)=0;D2:D9;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
 

Attachments

  • Guru Example.xlsx
    10.6 KB · Views: 9
Great formula :thumb:

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;D2:D9;0);0))


Andrej
 
Back
Top