-1

I'm trying to make a basic SUM of offsetted cells. Basically I have 4 columns, column A being just a title, column B being a counted number, and columns C and D referencing another value.

The thing I'm trying to accomplish is the number count on column G. It should be a SUM of all of the values in column B IF that number appears in the reference columns

enter image description here

An example would be that G2 would equal 15, since 1 appears in C2 (so it should add 7 from B2) and D3 (and add 8 from B3).

I have the basic =COUNTIF function to count the instances, but how do I reference the value from column B if a value appears in column C or D?

=COUNTIF(C2:D4,"1") =COUNTIF(C2:D4,"2") ...

https://docs.google.com/spreadsheets/d/1h9PDZii5wO866Hky3P_BZ_SOwuHAAd6qUySkuJ14L6A/edit?usp=sharing

Nicholas Hazel
  • 3,758
  • 1
  • 22
  • 34

3 Answers3

2

You can use SUMPRODUCT.

So long as Reference1 and Reference2 will not have the same number in the same row:

=SUMPRODUCT((($C$2:$C$4=F2)+($D$2:$D$4=F2))*$B$2:$B$4)

If it is possible for them to have the same number, but you only want to count the value in the Count column once, then:

=SUMPRODUCT(((($C$2:$C$4=F2)+($D$2:$D$4=F2))>0)*$B$2:$B$4)
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Was trying to figure out how to do it using one `SUMPRODUCT` instead of summing two. – Tom Sep 01 '17 at 22:03
1

SUMPRODUCT will get the answer for you as well

=SUMPRODUCT($B$2:$B$4,--($C$2:$C$4=F2))+SUMPRODUCT($B$2:$B$4,--($D$2:$D$4=F2))

Example

Tom
  • 9,725
  • 3
  • 31
  • 48
0

Single formula solution:

=QUERY( {"Reference #","Number"; TRANSPOSE(SPLIT(TEXTJOIN(",",0,TRANSPOSE(C2:D4)),",")), TRANSPOSE(SPLIT(REPT(JOIN(",",B2:B4)&",",COLUMNS(C2:D2)),","))}, "select Col1, sum(Col2) group by Col1")

The result is:

Reference #   sum Number
      1             15
      2              2
      3              2
      4              8
      5              7
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81