Trying to Sum using varying criteria

Hello,
I have the following formula that returns a single number rather than the sum of the column.

=SUMIFS('coversheet'!c8, "*Cleaning", 'Brand Value'!I4:I25) the out come of this is 5

If I sum column =sum(I4:I25) = 70

The reason I need the criteria selection in cover sheet is that this criteria changes but the sum of the column will change as items are not calculated, dependent on the C8 criteria.

I have tried =SUMPRODUCT, returns a #value error
I have tried =SUMIF, returns single value of 5

Can somone help with this?

Thank you
Chris

Your first argument should be a range, not a single cell. Did you mean =IF('coversheet'!c8="*cleaning",sum('Brand Value'!I4:I25),0) ?
Perhaps post a sample sheet to show what you want to accomplish

Thanks for your reply.
in Worksheet named "Cover Sheet" at Cell C8 this is a cell where I have a list item selection I.E., Cleaning, Commercial Cleaning, Home Cleaning, Garden, Lawn, Fence etc so singular cell reference.

in Worksheet named "Brand Value" I give a score for each item in Column I. there are 25 items with a possible score of 5 per item totaling 70.

So if Cell C8 on Worksheet "Cover Sheet" has *Cleaning then I want column I4 to I25 on Worksheet named "Brand Value" to be summed.

Hope this makes sense, sorry don't know how to post a worksheet
Regards
Chris

To post a sheet, click on " Reply to thread", then " Go Advanced then " Manage attachments" and follow the wizard

There is also the FAQ if necessary

Sample sheet attached I hope

Could you explain what relation there is between the two sheets ( common values)