compare cells and sum if equal

[KBARNET07]

sheet 2 total1 = sum of sheet1 cell d2 if sheet1 cell b2 = sheet1 cell c2; plus
sum of sheet1 cell d3 if sheet1 cell b3 = sheet1
cell c3; plus
the d2, b2, c2 cells go up to 30 or 40, but the total is the sum of all of
the equals using the number in the c2 thru c30 or c40 cells. example follows:
B C D

XY XY 5
CD SD 21
22 22 10
TOTAL1 = 15 BECAUSE XY=XY AND 22=22. And there can be 30 to 50 columns.

 

[mikerickson]

The formula
=SUMPRODUCT(--(A1:A50=B1:B50),C1:C50)
should do it.

Expand the ranges if nessesary (they must all be the same size), but SUMPRODUCT needs an explicit range, not full columns like A:A.

 

[Dave]

Hi,
Not sure if I understand your question fully, but the following would give
you the answer you want (15) from the sample data:

=SUMPRODUCT(--(A1:A3=B1:B3)*C1:C3)

Change A1:A3, B1:B3 and C1:C3 to suit your actual data.
Note that SUMPRODUCT requires all columns to be of equal length.
Regards - Dave.

 

[KBARNET07]

Dave, thanks. But I have to change something. Here are the new columns:
A B
ss ss
12
rt dt
2
yh yh
7
Answer=12+7=19. Is there a way to do a range on the compares and the sums
that will not give invalid data checks?

 

[mikerickson]

If the 12,2 and 7 are in column A,
=SUMPRODUCT(--(A1:A100=B1:B100),A2:A101)