How to sum values in one column excluding cells with duplicate text values in two different columns?

[kakehavata]

Hello, forum! I'm new here.

I have the following basic problem. In this table I have three variables:

I am using the SUMPRODUCT formula to calculate the sum of all numbers in column C that have a particular index in column A. For example, C13 =SUMPRODUCT(($A1:$A7="A")*(C1:C7))
I would like to exclude all duplicates in terms of all columns, that is C1 and C3, for example, should be counted only once (C13 would then = 7).

What should I add to the formula?

Thank you!

 

[jtakw]

Hi,

Your original formula could have been achieved by using just SUMIF, like below:

=SUMIF(A1:A7,A13,C1:C7)

For you new requirement, try this:

Book3.xlsx
	A	B	C
1	A	carrots	3
2	B	potatoes	2
3	A	carrots	3
4	C	apricots	1
5	D	peaches	2
6	A	carrots	2
7	A	apples	2
8
9
10
11
12
13	A		7
Sheet879

Cell Formulas
Range	Formula
C13	C13	=SUMPRODUCT((A1:A7=A13)/(COUNTIFS(B1:B7,B1:B7,C1:C7,C1:C7))*C1:C7)

 

[jasonb75]

You could use this, array confirmed with Ctrl Shift Enter.

=SUM(IFERROR(C1:C7/COUNTIFS(A1:A7,"A",B1:B7,B1:B7,C1:C7,C1:C7),0))

or with office 365

=SUM(INDEX(UNIQUE(FILTER(A1:C7,A1:A7="a",0)),,3))

 

[kakehavata]

Hi,

Your original formula could have been achieved by using just SUMIF, like below:

=SUMIF(A1:A7,A13,C1:C7)

For you new requirement, try this:

Book3.xlsx
	A	B	C
1	A	carrots	3
2	B	potatoes	2
3	A	carrots	3
4	C	apricots	1
5	D	peaches	2
6	A	carrots	2
7	A	apples	2
8
9
10
11
12
13	A		7
Sheet879

Cell Formulas
Range	Formula
C13	C13	=SUMPRODUCT((A1:A7=A13)/(COUNTIFS(B1:B7,B1:B7,C1:C7,C1:C7))*C1:C7)

Thank you very much, that solved the problem to a large extent. But what if I want to include cells, which do not contain data (empty cells) like:

OR:

In some cases I get the #DIV/0 message. Can't they simply not take the empty cells into account?

Thank you!

 

[jasonb75]

But what if I want to include cells, which do not contain data (empty cells) like:

Both of my suggestions work with empty cells, although the second one can give false results if there is a quantity with no description.

 

[kakehavata]

Both of my suggestions work with empty cells, although the second one can give false results if there is a quantity with no description.

For some reason I am not getting a result with the first formula:

(sorry for not using XL2BB, there seems to be a bug)

 

[jasonb75]

You haven't array confirmed it,

You could use this, array confirmed with Ctrl Shift Enter.

When you only press enter it doesn't work correctly.

 

[kakehavata]

You haven't array confirmed it,

When you only press enter it doesn't work correctly.

Sorry, I'm still a noob!

Worked perfectly!!! Thanks

 

[kakehavata]

One more question: what if I want to add other variables using the COUNTIFS function?
So, for example, if we consider columns D and E:

The yellow rows would have to count as one.

Thanks!

 

[Peter_SSs]

sorry for not using XL2BB, there seems to be a bug

I assume that you are following all the instructions described here.
What are the symptoms of the bug?
At what point in those instructions does the bug occur?