View Single Post
 
Old 02-06-2024, 09:47 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 875
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Karen615 View Post
Could you be so kind as to explain how these formulas work?
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.
Reply With Quote