Feb 25, 2021
How Do I Add Up Multiple COUNTA Values?
I have been having issues trying to add multiple COUNTA values together. I tried to use SUBTOTAL, SUM, and COUNT formulas however I am running into parsing errors.
To give some background, I have multiple COUNTA values that will change over time as I add more data to my sheet. COUNTA works well when I have them individually showing separate formulas for separate ranges. I want to be able to have all the COUNTA ranges that I have be added together into one cell. The sheet has multiple formulas setup that work with each other.
It looks like SUBTOTAL would be the formula to use but I keep running into errors. Maybe someone can help guide me in the right direction?
Example of the SUBTOTAL Formula I tried
=SUBTOTAL(3, Example!$AG$7:$AG , Example!$AH$7:$AH , Example!$AI$7:$AI , Example!$AJ$7:$AJ , Example!$AK$7:$AK , Example!$AL$7:$AL , Example!$AM$7:$AM , Example!$AN$7:$AN , Example!$AO$7:$AO , Example!$AP$7:$AP , Example!$AQ$7:$AQ)
If I can get this working, my goal would be to then have the SUBTOTAL be divided by another SUBTOTAL value(Or a hard number, whatever works) and converted into a percentage EG. SUBTOTAL(...)/SUBTOTAL(...)
Thank you
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Feb 25, 2021
Feb 25, 2021
You said you encountered parsing errors which indicates the formula was just not put together correctly. Based on your example you might also be over complicating things.
If you plan on keeping your COUNTA functions you can just use SUM() that points to each of these cells.
Ex: if all COUNTA are in row2 and in columns A through J
=SUM(A2:J2)
Alternatively you can simplify your SUBTOTAL() example
=SUBTOTAL(3, Example!AG7:AQ)
This would eliminate the need for individual count functions.
I only tested on a very small sample of data but got accurate results.
If you'd like more help please share an editable sample sheet that highlights what you're trying to do with like data.
Original Poster Neimat marked this as an answer
All Replies (2)
You said you encountered parsing errors which indicates the formula was just not put together correctly. Based on your example you might also be over complicating things.
If you plan on keeping your COUNTA functions you can just use SUM() that points to each of these cells.
Ex: if all COUNTA are in row2 and in columns A through J
=SUM(A2:J2)
Alternatively you can simplify your SUBTOTAL() example
=SUBTOTAL(3, Example!AG7:AQ)
This would eliminate the need for individual count functions.
I only tested on a very small sample of data but got accurate results.
If you'd like more help please share an editable sample sheet that highlights what you're trying to do with like data.
Original Poster Neimat marked this as an answer
Feb 28, 2021
Simplifying my multiple COUNTAs worked! The goal was to have a high level overview of the progress of data that was input and the percentage completed.
So what I did:
- In my Resources page I had a cell with =COUNTA(Example!AG7:AQ). (Resource!AO35 is the cell. This is showing data that has been entered in the designated field to show it has been filled with some sort of data.)
- Right next to that cell for organizational purposes I than did =SUM(TotalNumbers!$P2:$Z2) (Resource!AP35 is the cell. The total amount of cells that can have data in it.)
- On my Overview page I then had a cell =Resource!$AO35 & "/" & Resource!$AP35 & " = " & (Resource!$AO35/Resource!$AP35)
That shown me the data that I was trying to extrapolate! I couldn't get it to show me the value in percentage, but that's a simple conversion I can do in my head as the heavy lifting of math is done already.
I really appreciate you helping out and getting my thought process in the right direction.
Last edited Feb 28, 2021