google sheets
In google spreadsheets, after typing in a list of items through cells A1 to A7, I went off to another cell and typed the following formula
=COUNT(A1:A7)
It reports zero, instead of seven.
I've done exactly what this article does, but still not the correct result.
WOW, I was going to get you an exact solution, Google Docs is so frustrating. Script-loop City.
You can do this with the FILTER worksheet function, with your arrayCondition_1 being a formula of functions that gives a true value for each line that isn't duped. I was able to see that the MATCH function couldn't be used.
If you add the following formula in the second sheet (A1).
=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")
then the table will appear as you want.
Here an explanation of the formula, from the inside to the outside:
PIVOT C transposes the unique results from column C (turning rows into columns)GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)WHERE B IS NOT NULL ignores empty cellsSUM(B) adds the result of the querySELECT A simply displays column A, as unique values If you're only interested in individual results, then I suggest using the following formula:
=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")
Here an explanation of the formula, from the inside to the outside:
FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".SUM function will add them together.IFERROR will leave a blank cell if an error occurred (not result).
See example file I've prepared, where both samples are present: SUM data based on two variables
The formula in the example file is a bit different (the range is set to contain complete columns).
Best Answer
According to https://support.google.com/docs/answer/3093620 COUNT function only counts numeric values. Try COUNTA and it should do the job (explained here https://support.google.com/docs/answer/3093991)