Here is the response I get from the paid version (4o):
=========================
To count all entries in column A across multiple sheets in LibreOffice
Calc, you can use the SUMPRODUCT and COUNTIF functions in combination.
Here's how you can do it:
Step-by-Step Solution:
1. Name Your Sheets: Ensure your sheet names are correct and consistent
with your example (A, B, C, D, ..., W).
2. Insert the Formula in a Cell: Use the following formula in a new cell to
count all non-empty entries in column A across your sheets:
=SUMPRODUCT(COUNTIF(INDIRECT({"A.A:A.A", "B.A:A.A", "C.A:A.A", "D.A:A.A",
"E.A:A.A", "F.A:A.A", "G.A:A.A", "H.A:A.A", "I.A:A.A", "J.A:A.A",
"K.A:A.A", "L.A:A.A", "M.A:A.A", "N.A:A.A", "P.A:A.A", "R.A:A.A",
"S.A:A.A", "T.A:A.A", "V.A:A.A", "W.A:A.A"}), "<>"))
3. Explanation of the Formula:
COUNTIF: Counts non-empty cells in the specified range (column A for each
sheet).
INDIRECT: Dynamically creates references to the ranges in each sheet.
SUMPRODUCT: Adds up the counts for all sheets.
4. Adjust for Different Sheet Names: Replace A, B, C, ..., W with your
actual sheet names if they differ. Ensure all sheet names are enclosed in
quotes and separated by commas within the INDIRECT function.
5. Confirm the Formula: Press Enter, and Calc will return the total count
of non-empty entries in column A across all specified sheets.
Let me know if you need further clarification!
≠=========================
--
Thanks,
Alexander
Sent from my Google Pixel 7 Pro
On Thu, Nov 21, 2024, 17:45 Michael via PLUG-discuss <
plug-discuss@lists.phxlinux.org> wrote:
> chapgpt doesn't know what it's talking about. how do I count all entries
> in column A across all sheets in libre calc? the sheets are A B C D E F G
> H I J K L M N P R S T V W .
> The formula it gives me is saying 20 when I know there is over 100. Here
> is the formula it gives me:
> =COUNTA(a.a) + COUNTA(b.a) + COUNTA(c.a) + COUNTA(d.a) + COUNTA(e.a) +
> COUNTA(f.a) + COUNTA(g.a) + COUNTA(h.a) + COUNTA(i.a) + COUNTA(j.a) +
> COUNTA(k.a) + COUNTA(l.a) + COUNTA(m.a) + COUNTA(n.a) + COUNTA(p.a) +
> COUNTA(r.a) + COUNTA(s.a) + COUNTA(t.a) + COUNTA(v.a) + COUNTA(w.a)
> --
> :-)~MIKE~(-:
> ---------------------------------------------------
> PLUG-discuss mailing list: PLUG-discuss@lists.phxlinux.org
> To subscribe, unsubscribe, or to change your mail settings:
> https://lists.phxlinux.org/mailman/listinfo/plug-discuss
>
---------------------------------------------------
PLUG-discuss mailing list:
PLUG-discuss@lists.phxlinux.org
To subscribe, unsubscribe, or to change your mail settings:
https://lists.phxlinux.org/mailman/listinfo/plug-discuss
(text/plain)