# Dynamically sum the value of sets of cells separated by an empty row?

Not sure how to word this, but is there a good way to dynamically sum the value of sets of cells separated by an empty row? Or anything to that effect. E.g.:

| Person A | approved | 1/25 |
| Person A | approved | 1/26 |
|          |          |      |
| Person B | denied   | 1/29 |
| Person B | denied   | 2/02 |
| Person B | approved | 2/12 |
|          |          |      |
| Person C | denied   | 1/25 |
|          |          |      |


I would like a cell to sum up the number of approvals for each person. However, I will continually add rows for each person. I do not want to move Person B or C to a different spreadsheet (unless there is no good solution) or shift them to different columns. The problem is I cannot specify a range of cells because I will keep adding entries over time. I cannot specify the entire column either as I want the data of Person A, B, and C to be structured in this way on the same spreadsheet in order to easily compare the data of each person.

edit retag close merge delete

Sort by » oldest newest most voted

Hello @zf

Assuming A column is for Person and B column for status:

=SUMPRODUCT(A:A="Person A";B:B="approved") - counts Person A approvals

=SUMPRODUCT(A:A="Person B";B:B="approved") - counts Person B approvals

And so on. Or:

=COUNTIFS(A:A;"Person A";B:B;"approved") - counts Person A approvals

=COUNTIFS(A:A;"Person B";B:B;"approved") - counts Person B approvals

more

@SM_Riga Thanks. If I wanted to test that one of the criteria is a number (or better yet, a date, but I'm not sure how that's possible), how would I do it with COUNTIFS since it is way more performant for me than SUMPRODUCT? E.g. the COUNTIFS equivalent of SUMPRODUCT(A:A="Person 1",G:G="P",ISNUMBER(P:P),P:P>EDATE(TODAY(),-12))?