how to count cells with values but ignore blank cells with formula and in a month?

I am working on daily services dashboard. in which i will show how much days a sales man provide services and how much days no service provided in a month.

for example there are 24 working days in a month and sales man provided services in 21 days the remaining days will be count as no service provided. first formula will count total number of working days in a month (Monday to Saturday) and than it will show days of services provide or not.

Hope you guys understand.

Sounds like you need a COUNTIF setup? Not sure how you have it set up though. Is there a sample?

With Month Start/End dates in A1 and B1:

Number of working days ..

=NETWORKDAYS.INTL($A1,$B1,11)

OR

=NETWORKDAYS.INTL($A1,EOMONTH($A1,0),11)

You can add holidays to the above if required.

I don't know how you record "Service Days" but COUNTIF or COUNT/ COUNTA should do what you require.

Try

=SUMPRODUCT(--(WEEKDAY(B1:B28,1)<>1)*(C1:C28<>0))

Only counts working days > 0

i am working on data (daily sale). now my boss need to know if there is working day and no sale on that day, than total how much working days are in a month in which no sale.

final i got an idea. if i use this formula

Formula:

Please Login or Register  to view this content.

than count working days using formula in another cell

Formula:

Please Login or Register  to view this content.

and than subtract both finally i got the result of how much days Salesman did not sale any product.

sample is attached

....final i got an idea

and I suppose it is pure coincidence that YOUR idea is identical to my posts???

OR

=SUMPRODUCT(--(WEEKDAY(B1:B28,1)<>1)*(C1:C28=0)*(B1:B28>=H2)*(B1:B28<=I2))

in one formula

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks JohnTopley thats what i want dear thank you once again.