SUMPRODUCT Function with varying criteria

I have a random assortment of 1s and 2s from D3:D34 and from E3:E34. I have the following formula =SUMPRODUCT(--(E3:E34=$D3:$D34)) summing the amount of rows in Column D that match their counterpart in Column E. I want to make one specific row count as two rather than one (it does not matter which row this is). How can I adjust this formula to account for this, or do I need to make another formula entirely?

Hi,

Not sure if I understand your question, if it doesn't matter which row, can't you just add 1 to the result of your formula?

"Your SUMPRODUCT formula"+1

I only meant that it doesn't matter which row in the sense that, if you were to make an example formula, it wouldn't matter which row you decided to use. So, just for example, let's say that I want to make row 14 worth 2 instead of 1 in the total summation. How would I go about implementing that?

Still not understanding, so if you assign row 14 to be worth 2, does that mean Only if it's a match (Column D & E), if it's Not a match, then it's worth 0, right?

Yes, that would be correct. I really wasn't sure how to put that into words, sorry.

Well, I think this is what you're saying:

=SUMPRODUCT(--(E3:E34=$D3:$D34))+IF(E14=D14,1,0)

Yeah, that did the trick. I couldn't wrap my head around it, but thank you!

You're welcome, thanks for the feedback.

Will that row assignment (14) change from time to time?
In other words, today is row 14, tomorrow, you might want it to be row 20, then later, row 32, etc.?

Yes, it's for a college/pro football pool, where one of the 32 games each week is given a value of 2. I wanted to be able to change which game was worth that bonus.

I'll be signing off for the night shortly, so in case you want to easily change the Row assignment, you can use a Cell reference rather than "hard-coding" the Row in the formula.

I used F1 as the cell housing the Row assignment that's to be worth 2 if it's a match, use whatever cell you want and adjust the formula accordingly, then anytime you want a different row for the 2 point count, just change the number in the reference cell (i.e. F1):

Excel 2016 (Windows) 64 bit

	D	E	F
1			14
2
3	1	2	22
4	2	2
5	1	2
6	1	2
7	2	2
8	2	1
9	1	1
10	2	1
11	2	1
12	1	2
13	2	1
14	2	1
15	1	2

Sheet: Sheet72

Excel 2016 (Windows) 64 bit

	F
3	=SUMPRODUCT(--(E3:E34=$D3:$D34))+IF(INDIRECT("D"&F1)=INDIRECT("E"&F1),1,0)

Sheet: Sheet72