SUMIF with 2 unique records - urgh!

[mgavin]

I've got 3,000 row spreadsheet where I need to sum a column IF there is a new value in in 2 other columns. Example:

A B c
Huey apple 10
Huey apple 10
Huey orange 25
Dewey apple 30
Dewey apple 30
Dewey orange 15
Louie apple 40
Louie orange 3

so the total should be 10+25+30+15+40+3

any ideas?

[CARACALLA]

	A	B	C	D	E
1	Huey	apple	10
2	Huey	apple	10		123
3	Huey	orange	25
4	Dewey	apple	30
5	Dewey	apple	30
6	Dewey	orange	15
7	Louie	apple	40
8	Louie	orange	3

E2=SUM(IF(FREQUENCY(IF(A1:A8&B1:B8<>"",MATCH($A$1:$A$8&$B$1:$B$8,$A$1:$A$8&$B$1:$B$8,0)),ROW($A$1:$A$8)-ROW($A$1)+1),C1:C8))

Control+Shift+Enter

[mgavin]

WHOA and grazie! That is quite a formula. However, with 3K columns, I need a formula for a total of column C and not a total per row. Any other ideas Caracalla? ma molte grazie l'aiuto!

[FlameRetired]

But that is what CARACALLA's formula does

so the total should be 10+25+30+15+40+3

=123

CARACALLA formula)=123

What am I missing?

[mgavin]

I must not have explained it well enough, because I get a #VALUE! error. I don't know why. I even tried his formula in a new spreadsheet with the example, and I got the same error.

[FlameRetired]

That formula is an array formula and must be committed from edit mode with Ctrl + Shift + Enter and not just Enter.

You will know it is properly committed when you see curly braces {} around the formula in the formula bar. You do not type those in yourself. If you do you will get an error. Excel puts them in for you.

[mgavin]

I am just BLOWN away. Y'all are the BOMB!!!! MILLE GRAZIE!

[FlameRetired]

Glad to hear it works.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[davsth]

I am also amazed by the formula and it does what was asked, but if I change the third record to Huey apple 25, it returns 98. As it only sums the first record. However should it still be 123?

Only the asker can answer and this may be impossible to happen so irrelevant!

I guess it depends if the repeated records include their value as part of the uniqueness!

[mgavin]

Yes, the formula works for what I needed. thanks again. moving to solved.

[CARACALLA]

	A	B	C	D	E
1	Huey	apple	10
2	Huey	apple	10		123
3	Huey	apple	25
4	Dewey	apple	30
5	Dewey	apple	30
6	Dewey	orange	15
7	Louie	apple	40
8	Louie	orange	3

123 again IF



E2=SUM(IF(FREQUENCY(IF(A1:A8&B1:B8&C1:C8<>"",MATCH(A1:A8&B1:B8&C1:C8,A1:A8&B1:B8&C1:C8,0)),ROW($A$1:$A$8)-ROW(A1)+1),C1:C8))

Control +shift +enter