CODEWITHSUNDEEP
excelexcel-formulacalculated-columns
wittman
wittman

Reputation: 305

Sum and recurrence for columns

i have 3 columns: A has names, B has numbers, C has names. I want to see how many times MIKE and JANE appears in the table and how many times they met. The order of the columns is not fixed so i can modify the order of the columns (the names to be in columns A and B and the numbers in C) . Any help is welcomed.

  -------------------------------------------------------------
  |     A  |  B  |   C   |  D  |  E    |  F    |  G  | H      |
  -------------------------------------------------------------
  | TRAINER|HOURS| CHILD |     |TRAINER| CHILD |HOURS|MEETINGS|
  -------------------------------------------------------------
  |  MIKE  |  2  | JANE  |     | MIKE  | JANE  | 10  |   2    |
  -------------------------------------------------------------
  |  MIKE  |  5  | STEVE |     | MIKE  | STEVE |  7  |   2    |
  -------------------------------------------------------------
  |  HARRY |  3  | JANE  |     | HARRY | JANE  |  3  |   1    |
  -------------------------------------------------------------
  |  MIKE  |  8  | JANE  |     | HARRY | STEVE |  7  |   1    |
  -------------------------------------------------------------
  |  MIKE  |  2  | STEVE |     |       |       |     |        |
  -------------------------------------------------------------
  |  HARRY |  7  | STEVE |     |       |       |     |        |
  -------------------------------------------------------------

Upvotes: 1

Views: 183

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

I am going to suggest a SUMPRODUCT option. As other have mentioned there is also the SUMIF option.

=SUMPRODUCT(($A$2:$A$7=$E2)*($C$2:$C$7=$F2)*$B$2:$B$7)

If you move your C column to B update the ranges in the formula accordingly.

Now the better option as other have suggested is:

=SUMIFS($B$2:$B$7,$A$2:$A$7,$E2,$C$2:$C$7,$F2)

And I totally missed that you were looking for the number of meetings as opposed to the hours. We can again use SUMPRODUCT with one less term or we can use COUNTIFS.

=SUMPRODUCT(($A$2:$A$7=$E2)*($C$2:$C$7=$F2))

and the COUNTIFS method is

=SUMIFS($A$2:$A$7,$E2,$C$2:$C$7,$F2)

Upvotes: 1

Related Questions