CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
staticz
staticz

Reputation: 13

Comparing Multiple Cells and Returning Values

I have a spreadsheet that looks like this:

  A     B          C   D
1 Bob   10/02/2013 10

2 Bob   10/02/2013 2

3 Bob   10/02/2013 8

4 Steve 10/01/2013 6

5 Steve 10/01/2013 6

6 Sally 10/01/2013 6

The requirement is that if A1 matches anything else in column A (A1,A2, and A3) AND B1 matches anything in column B (B1,B2,B3) Then add up the values C, divide by 4 and put that in D.

I want to do this for the whole sheet (500ish rows).

Upvotes: 1

Views: 522

Answers (1)

Santosh
Santosh

Reputation: 12353

You may use the SUMIFS function. Below is sample.

Formula =SUMIFS(C1:C5,A1:A5,A1,B1:B5,B1)

enter image description here OR

enter image description here

Or can use SUMPRODUCT function .

=SUMPRODUCT($C$1:$C$5,--($A$1:$A$5=$A1)*--($B$1:$B$5=B1))/4

enter image description here

Upvotes: 2

Related Questions