Countif formula to count days from a calendar table

0
votes

I have an excel table in the form of a calendar. The table shows how much material an "Area" used on any given day. Cell AI16 needs to look at each date, and if any material was used by any Area, count it as a "Day attended".

E.G - In the example in the image linked below, All Areas used material on the 1st, 20th and 21st. Only Area 6 used material on the 26th. Column AI16 should count this as 4 days attended. The countif formula i'm currently using is only counting this as 3.1 days attended due to not all Areas using material on the 26th.

Any help would be hugely appreciated.

Materials Used Table

1
excelexcel-formula
have a read here[ablebits.com/office-addins-blog/2014/07/10/… but I will try and do the following formula =COUNTIF(A1:O1,">0") this will count within the range where values are more than 0 – Tim
Showing your formula you are currently using might allow us to show you where a correction needs to be made. COUNT, COUNTA, COUNTIF and COUNTIFS return integers so it seems like there is more than just a count function being used. – Forward Ed

1 Answers

0
votes

Option 1 Helper Column

Create a helper column arbitrarily in column AH to count the days where something was used in each row. Place the following formula in AH16 and copy down to AH22:

=COUNTIF(B16:AF16,">0")

Then in AI16 use the following formula:

=MAX(AH16:AH22)

Option 2 Single Cell formula

Since you are totaling daily use in row 23, the helper column can be skipped and simply count the number of days used in the total row. Place the following formula in AI16. Note in the example below it was place in AI17 for demonstration purposes.

=COUNTIF(B23:AF23,">0")

enter image description here

This solution is based on negative material use is not possible. Adjust ranges to suit your needs.