If there is a number in cell E3 but not in J3 I want it to count 1, however if there is a number in both I want it to return a 0.
Ultimatly I want to complete this is an entire column but have it compared only by row to row (E3/J3, E4/J4, E5/J5 etc...)
Hi,
I'm trying to work out how to include an instruction to count the results of this query, within the query itself rather than it creating a list that I then have to use =COUNTA to tally the results: =query(Tracker!A4163:R4233, "select L, N WHERE L IS NOT NULL AND N <500"). I basically want to count the number of times within a range that there is any data in row L along with the number in row N is <500. Hope that makes sense?
So I need to count the number of dates in a column, but some rows have more than one date in the column. My idea was to join them all into one cell, split them by the common delimiter, and then count all the cells from that joining and splitting. It gives the expected value any of the cells have a date, but returns 1 if the cell is empty. Could someone please explain why?
https://docs.google.com/spreadsheets/d/1zD7dKStl7Ex_c0fIbxvY6q2OlSAGjSS_TCqcYucAeHg/edit?usp=sharing
What’s the difference between countunique and count(unique (range))?
I’m getting different results by using both functions but don’t know what it means
Guys! Today I noticed something supper odd. And now I wander was it always like this? Here's the issue/bug: in both Excel and GSheets the COUNT(range) and COUNTIF(range, "<>") formulas are counting the cells even if they have formula that outputs "" Blank results! WHATA HACK IS EVEN THAT?! Furthermore, at the bottom right corner when i selected a range with 10 blanks results and 10 non-blank results, the COUNT is also showing 20!
I'm new to this, and this seems like a simple answer but I cant figure it out. I'm trying use a formula to show the amount of yes and no's in a couple different sections. It's working, but the data shows up as a decimal point out of 1, I want a simple Percentage out of 100. Is this possible?
The formula I'm using currently is =IFERROR(COUNTIF(X:Y,"YES")/COUNTA(X:Y))
Hi, I am updating a sheet that includes this formula: =countifs(P14:P,"<=500",O14:O,"1") but I want to change the data within col O to the price that was quoted for that particular job (like £495) instead of just "1" which is currently being used to denote that we won that particular job. Is it possible to get the formula to count the cells in col O that have data in, as a COUNTA would?
O14:O,">"""Or
O14:O,"<>"I personally prefer the former because it excludes "" from a formula result, for example: IF(A:A="x", "", "y"), but the latter is closer to how COUNTA works
=COUNTIFS(P14:P,"<=500",O14:O,">0")Alt
=COUNTIFS(P14:P,"<=500",O14:O,"<>")Or
=COUNT(FILTER(O14:O,O14:O,P14:P,P14:P<=500))Alt
=COUNT(FILTER(O14:O,O14:O<>"",P14:P<>"",P14:P<=500))Or
=COUNT(ArrayFormula(IF(O14:O * P14:P * (P14:P<=500),1,)))I’ve been using COUNT for years without realizing it quietly skips text cells — only counts numbers!
So if your dataset has words like “Yes” or “N/A”, you’ll need =COUNTA() instead, which counts all non-empty cells.
COUNT → counts only numeric cells
COUNTA → counts everything that’s not blank
It’s a tiny detail but super important if you’re summarizing survey data or attendance sheets.
I made a short 30-sec clip showing it in action here if anyone wants to see the difference visually 👇
🎥 https://www.youtube.com/shorts/pd_9ng_7EAQ
What’s another Excel formula you think people commonly misunderstand? I’m thinking of doing a mini-series on these small-but-powerful differences.
If you like bite-sized Excel tips, I’ve been collecting all of them here:
https://www.youtube.com/playlist?list=PL5w9hG_JDbyjTCBFAdRVobtQVZD1PvQRt
Hey Y'all
I'm trying to use the =COUNTA formula to count the number of entries from a different sheet within a file. I tried =COUNTA(Sheet2!A2:A25) but it didn't work. The Sheet I'm trying to pull from is the 2nd sheet but it's also titled, "Raw Data (Do Not Edit)" I tried =COUNTA(Sheet'Raw Data (Do Not Edit)'!A2:A25) and it didn't work either.
Sorry if this is a super obvious problem but I'm struggling to figure it out!!
Hey I tried countif, countifs and counta but it didnt work. Is there a way to combine counta with countif?
I want to know how many clients come from a referral. The name of the clients are always different.
I tried =Countifs(B2:B7;Counta(B2:B7);C2:C7;E2) And it gives a 0.
Mainly because of B2:B7;Counta I think
I don’t want the sum of the numbers in the cell I was hoping for a formula that counts the cells being used. Example: if my range is C1:C:9 and all the cells have a number in it I was the total to then be 9.
Thanks!