CODEWITHSUNDEEP
excelexcel-formulasumifs
Zach Brown
Zach Brown

Reputation: 63

Excel SUMIF using current row

I've never had to come here for the various Excel conundrums I've had in the past, but this one has me stumped. It has to do with this table :

table screenshot

I want to get the total amount of assists I have, but only in games that I have won (Goals For > Goals Against). The total in this table would be 4+2+5+0+2=13.

This has proven tricky, because I want the result of 13 to be returned to a single cell, so referencing the current row during the calculation isn't as easy as having a column of formulas that I can drag down to account for each new row.

I've read answers suggesting VLOOKUP, INDEX(MATCH()), etc. but they seem to be addressing problems similar, but not exactly alike to mine.

Thanks in advance for the help!

Upvotes: 1

Views: 851

Answers (2)

Mrig
Mrig

Reputation: 11702

You can use SUMPRODUCT as:

=SUMPRODUCT((A2:A11>B2:B11)*(C2:C11))

See image for reference:

enter image description here

EDIT : ______________________________________________________________

SUMPRODUCT function multiplies arrays in formula and returns the sum of those products.

So as per data in the question, formula will be evaluated as follows:

Step 1: Calculate (A2:A11>B2:B11)

This will give you array as:

{(A2>B2);(A3>B3);(A4>B4);(A5>B5);(A6>B6);(A7>B7);(A8>B8);(A9>B9);(A10>B10);(A11>B11)}

which is

{(4>1);(4>1);(1>3);(6>2);(0>4);(2>1);(3>2);(1>4);(2>3);(2>6)}

and is equivalent to 

{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}

This is same as

{1;1;0;1;0;1;1;0;0;0}

Step 2: Calculate (C2:C11)

This will give you array as:

{4;2;0;5;0;0;2;1;1;2}

Step 3: Calculate (A2:A11>B2:B11) * (C2:C11)

This will calculated as:

{1;1;0;1;0;1;1;0;0;0}*{4;2;0;5;0;0;2;1;1;2}

which is equivalent to

{1*4;1*2;0*0;1*5;0*0;1*0;1*2;0*1;0*1;0*2}

and will give

{4;2;0;5;0;0;2;0;0;0}

Step 4: Calculate SUMPRODUCT((A2:A11>B2:B11)(C2:C11))*

This is equal to

SUMPRODUCT({4;2;0;5;0;0;2;0;0;0})

and will be calculated as

4+2+0+5+0+0+2+0+0+0 = 13

For details on SUMPRODUCT see this.

Upvotes: 2

Kyle
Kyle

Reputation: 2545

I'm assuming "Goals For" is column A "Goals Against" column B and "Assists" column C. I would us an array formula if there isn't a ton of data.

{=SUM(IF(A:A>B:B,C:C,0))}

Make sure you use Shift+Ctrl+Enter to enter the formula since this is an array formula.

Upvotes: 0

Related Questions