sumproduct (range;range;value)

[pinstripe]

i am creating a worksheet that adds the totals for an entire range (column), wherein any cell within the column that contains a certain number is added to the cell.

i get this part. i used =sumif('sheet1'!A1:A100,"C4",'sheet1'!B1:B100). works perfect but,
How can i SUM everything IF A1:A100 equals anything in C4:C80
SUMIF(A1:A100;range;B1:B100)
it does not work even with sumproduct
Any idea?

[VBA Noob]

Hi,

How can i SUM everything IF A1:A100 equals anything in C4:C80

Do you mean the sum of C4:C80 equals the sum of A1:A100 ??

VBA Noob

[daddylonglegs]

You can use

=SUMPRODUCT(SUMIF(A1:A100,C4:C80,B1:B100))

but this will double count if any values are duplicated in range. To avoid that

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A100,C4:C80,0)),B1:B100)

[pinstripe]

You can use

=SUMPRODUCT(SUMIF(A1:A100,C4:C80,B1:B100))

but this will double count if any values are duplicated in range. To avoid that

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A100,C4:C80,0)),B1:B100)

Thank you both for the quick answer. It works perfect.

One question more though (or should i open new thred for this?):
i have a range C4:C50 (range name "one") how can i get that name out in cell C3? Couse i would like to use combo to show (or to pick) nemes of ranges C3:F3 (names: one, two,...)