Subtotal/Aggregate Not Working Help

Hello!

Preface: I am not very skilled at Excel, I rely heavily on Google results; I may not understand all of the lingo.

I work in a rental house, and we do billing weekly and pro-rate for a number of reasons (i.e. for Canada day they will not be working so we go from 1 week to 0.8).

Not every client has the same rental period, some are 20+ weeks and others are 3. I wanted to be able to filter the data, and only have it calculate the weeks shown.

The issue I'm running into is the nested IF function I used to calculate the pro-rating shows results as TEXT rather than NUMBER. Since I want to filter the results, I can't use =sum(value(range)); and subtotal and aggregate aren't factoring in the decimals.

Is there a way so I can have my cake and eat it too? Can we add VALUE to the formula on Subtotal or Aggregate?

I would suggest changing all your text to numbers.

Type a 1 in a blank cell somehwere and copy that cell.
Then select all your billing weeks and paste special selecting 'mulitply' as your operation.

This converts them all to numbers.

Next, your subtotal is giving you a count of all cells with a value in them whereas you actually want a sum type of subtotal not including the ones you have hidden.

So use =SUBTOTAL(109,(Sheet1!$F$3:$F$10))

For part 1, I don't have 'Multiply' as a special paste option. I only have the below:

Paste, Formulas, Formulas & Number Formatting, Keep Source Formatting, No Borders, Keep Source Column Widths, Transpose, Merge Conditional Formatting, Values, Values and Number Formatting, Values and Source Formatting, Formatting, PictureAttachment 812672

Do you know where I can find Paste Special - Multiply?

I suppose there is no way of keeping my if functions to auto calculate the por-rating?

Thank you, Croweater!

That made it work completely as desired. I really appreciate your help.

No problems at all. Thanks for the feedback.