SOLVED

# Counting cells with formulae in them

Copper Contributor

# Counting cells with formulae in them

I have a spreadsheet where I am entering 3 elements of wages (basic, holidays, overtime) then totalling the gross pay for each week then again over a period of 13 weeks. I need to average what each person earned per week. However not everyone worked all 13 weeks so I want to count how many weeks they worked and use that in a new formula. The COUNT function doesn't like me clicking on the totals for each week to work out the average. I don't really want to do this manually as there are over 150 employees. Any tips please?

5 Replies

# Re: Counting cells with formulae in them

I don't understand why COUNT wouldn't work with cells that contain totals, but to count the number of cells in myrange that contain formulas, you can use

=SUM(--ISFORMULA(myrange))

# Re: Counting cells with formulae in them

I'm quite an amateur but I'm sure there must be a way!

In column AJ I want to show how many (in this case) fortnights have been worked but it really doesn't like any formula I use as W3, for example, is T3+U3+V3. So when I am trying to put formula into AJ I'm putting =COUNT(W3+AA3+AE3+AI3) but it doesn't like it as it normally wants a range of W3:V3 but that would then include all the columns in between which I don't want.

If you can steer me and tell me where I'm going wrong I'd appreciate any help thank you!

# Re: Counting cells with formulae in them

best response confirmed by Fiona_Myatt (Copper Contributor)
Solution

# Re: Counting cells with formulae in them

Change that formula to

=COUNT(W3, AA3, AE3, AI3)

# Re: Counting cells with formulae in them

Thank you! You're a hero!
1 best response

Accepted Solutions
best response confirmed by Fiona_Myatt (Copper Contributor)
Solution

# Re: Counting cells with formulae in them

Change that formula to

=COUNT(W3, AA3, AE3, AI3)