Ask Your Question

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

asked 2020-03-25 22:00:00 +0100

zf gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-03-25 22:28:51 +0100

updated 2020-03-25 22:34:36 +0100

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

edit flag offensive delete link 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))?

zf gravatar imagezf ( 2020-04-03 07:42:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-25 22:00:00 +0100

Seen: 99 times

Last updated: Mar 25 '20