# Formula for subtotal based on a field

I am sure this is available somewhere but K could not figure out how to make it - I am looking to create a summary based on a key field. Rather than be confusing, here is what I am working with and what I hope to achieve.

DATE STAFF COLLECTED SHARES CREDIT DEBIT
25-Aug JEAN 145.12 2 \$72.56
25-Aug BREANNA 145.12 2 \$72.56
25-Aug KAILEY 165.44 3 \$55.15
25-Aug ROBERT 165.44 3 \$55.15
25-Aug CHARLES 165.44 3 \$55.15
26-Aug CHARLES 297.38 3 \$99.13
26-Aug JEROME 297.38 3 \$99.13
26-Aug BREANNA 297.38 3 \$99.13
27-Aug CALEB 141.15 2 \$70.58
27-Aug JEAN 141.15 2 \$70.58
27-Aug KAILEY 233.34 2 \$116.67
27-Aug CHARLES 233.34 2 \$116.67
27-Aug JEROME 15.8 2 \$7.90
27-Aug ROBERT 15.8 2 \$7.90
28-Aug CALEB 312.64 2 \$156.32
28-Aug JEAN 312.64 2 \$156.32

THE FORMULA I AM LOOKING FOR SUMS COLUMN E BASED ON COLUMN A
E.G. This is what I hope to get from the above table

DATE CREDIT DEBIT
25-Aug \$310.57
26-Aug \$297.39
27-Aug \$390.30
28-Aug \$312.64
• Your table formatting did not hold up in your post. I suggest that you share a link to a sample spreadsheet with the data already setup and your expected result hand-entered in the place you'd like to see that result rendered. It's usually the most efficient route to getting help, because the volunteer contributors can see everything we need to know, clearly and as a cohesive whole. Commented Sep 1, 2022 at 0:31
• @ErikTyler You're right but... 1) we've assumed that there are/should be 'DEBIT" values in Column F. But there aren't any. 2) a spreadsheet is not the only way of providing information. Markdown accepts tables now and tablesgenerator.com is an easy way to create the markdown to go into questions. Commented Sep 1, 2022 at 2:52
• Hi, welcome to WebApps. There are probably several ways that your problem could be approached. Would you please summarise any research that you've done and outline what you've done to solve the problem yourself Commented Sep 1, 2022 at 2:55
• @Tedinoz, I know this continues to be a matter of discussion here. I can only say that as a volunteer, it is not time-efficient for me to continue to ask questions in order to try to understand a post (as, even with the updated post structure, I still have questions). If a poster does not wish to share a link, no problem. But if I can't quickly garner all of the information from the post, I generally just move on and leave continued questioning to others who have the time and inclination. Commented Sep 1, 2022 at 2:57

You don't need a formula! Everything you need to solve your problem is in the menu Insert - Pivot Table.

Place this formula in `G1` or in any cell of another sheet (in this case you will have to add the name of your main sheet to the range of the formula)

``````=query(A1:E,"select A,sum(E) where A is not null group by A,1")
``````

If you also want to add up the debts, that column must not be empty (it must contain at least one numerical value) and the correct formula will be:

``````=query(A1:F,"select A,sum(E),sum(F) where A is not null group by A label sum(E) 'CREDIT', sum(F) 'DEBIT'",1)
``````

You will have a result like this:

• The OP data doesn't include any debit balance values. Would you please show how you get your result? Also, since there are no debit balances, your second formula generates an error "Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC" because column F has only a header and the formula "assumes" that the column contains strings. Commented Sep 2, 2022 at 2:52
• Precisely for this reason I wrote: If you also want to add up the debts, that column must not be empty (it must contain at least one numerical value) ... and, of course, I entered the debit values ​​into my test file. Commented Sep 2, 2022 at 4:07