Using Excel 365

I desperately need help. Please see attached.

Each time a site is inspected, a date is entered in column L. I need a formula (in cell R2) that sums the Principle dollar amount of sites inspected. Please see cell R2 for the actual result it should be.



Then I need the overall percentage to calculate as sites are inspected. Please see cell R3 for the actual result it should be. Five sites were inspected out of the grand total of 25 (cell C31).

I would GREATLY appreciate ANY help someone could offer.

Thank you in advance,
Karen

Attached Files

Plan2.xlsx (22.6 KB, 8 views)

Power Query Solution for total. Simple math for the other issue =COUNTIF(Table1[2024 Inspection],"<>"&"")/C31

Or
Btw. Alan
OP doesn't use defined Table, and has manually put subtotals into table (practically created an paper document in Excel!). This is the reason I checked the column A - to exclude subtotals.

Thank you so much for your help. This worked great!

Could you be so kind as to explain how these formulas work?

=SUMIFS(E2:E29,A2:A29,"<>" & "",L2:L29,">0")

=COUNTIFS(A2:A29,"<>" & "",L2:L29,">0")/COUNTIFS(A2:A29,"<>" & "",E2:E29,">0")

You mentioned: "I checked the column A - to exclude subtotals."
How does checking column A exclude subtotals?

Your help is greatly appreciated.

Thank you,
Karen

SUMIFS() and COUNTIFS() are like SUMIF() and COUNTIF(), but you can check for any number of conditions. So:

=SUMIFS(E2:E29,A2:A29,"<>" & "",L2:L29,">0")

SUMS all values in E2:E29 for rows, where cell in column A is not empty (excludes subtotal rows, as for those was nothing entered into column A, and where cell in column L is > 0 (there were inspection dates in column L, and dates in Excel are always positive numbers - so when there was no date entered, the value of cell is 0, and when there is a date, the value of cell is > 1)

=COUNTIFS(A2:A29,"<>" & "",L2:L29,">0")/COUNTIFS(A2:A29,"<>" & "",E2:E29,">0")

1st COUNTIFS counts all entries in rows 2:29, where cell in column A is not empty, and there is a date entered into column L (the entry was inspected);
2nd COUNTIFS counts all entries in rows 2:29, where cell in column A is not empty, and there was some monetary amount entered into column E (the entry was not subtotal, and there was some money accounted). Essentially it returns the count all account rows. And probably you better replace COUNTIFS(A2:A29,"<>" & "",E2:E29,">0") with COUNTIFS(A2:A29,"<>" & "",E2:E29,"<>0"), so the formula will work when there are negative amounts too;
And as a last step, the count of inspected entries is divided by total count of entries to get the inspection percentage.

And a warning! In case you enter anything (even a space string " ") into column A for some subtotals row, the formulas don't work anymore (i.e. return a wrong result). This is the reason for my remark you having a 'paper document' in Excel. Much better way is to have a separate sheet(s) dedicated for data entry only (without any totals and subtotals), and then any number of report sheets, which read info from data sheet(s), and display it in any design you fancy.
Or you can have some rows at top of data entry page with SUBTOTAL() formulas, which then return totals for filtered records in data entry table. When the table is not filtered. those formulas return e. g. count of all entries in table, or total of all monetary amounts. When you filter the data entry table to inspected rows to be displayed, same formulas return the count of inspected entries, or total amount of inspected entries. When you filter the data entry table by values in column B (the ones determining subtotals in your original table), you get the count and summary amount for this value used for filtering, etc.

WOW! Thank you soooo much! This is very helpful.

Have a great evening.

Karen