COUNTIF & SUMIF formula question (excel/forum newbie)

[RichardBewick]

I have a two part question relating to the same spreadsheet I am designing.

I have a scoring system I monitor in excel (I have attached an example speadsheet). I score against a number of criteria e.g. criteria 1 scores "2", criteria 2 scores "4". My problem is that the criteria don't always attract a score, sometimes they blank. I also need to specify which criteria I want to count in the scoring as they are not always relevant every time.

I need formulae that will take this into account and be ready to total up only when a score is there (COUNTIF?); and out of the range of scores I want to total up only the specified criteria at choice (SUMIF?).

My COUNTIF and SUMIF skills are failing with this which is very complex for my level.

What would the formula be in each instance? I have attached the file to reference the cells that I have questions about.

Question ?A: - is this a COUNTIF?
In this cell I want to calculate the number of occurrences where there is a score detailed, but only for specified criteria, for argument's sake criteria 1,2,3,4,7 & 8

The answer would be 5 in this instance, not 6, because Criteria "3" did not receive a score.

I would want to have other cells which would calculate the number of occurences where there had been a score but using different combinations of criteria

Question ?B: - is this a SUMIF?
In this cell I want to calculate the total sum of scores for those specified criteria where there has been a score
The answer would be 13 in this instance (3+2+4+1+3 from the scores that occurred for those specified criteria)
I would want to have other cells which would calculate the total sum of the scores but using different combinations of criteria

any help from the excel grandmasters would be appreciated

[royUK]

You should stick to one question per thread, this will get you better response, as will using a descriptive title as the Rules specify

[deadlyduck]

Hi- I'm attaching a possible solution.
I;ve inserted 'helper columns' to the right which allow you to specify if a particular criteria should be excluded eg in your given example, you excluded 5,6,9 and 10, so these have a "y" in the helper cells.
Let me know if this is of use.

[martindwilson]

where and how are you going to specify the criteria? in one cell, a cell for each a prefixed list what.?

[deadlyduck]

The attachment mentioned in my previous post was lost after a moderator hid my post (to encourage the OP to clarify the title of the query) so I'm posting it again.

[RichardBewick]

where and how are you going to specify the criteria? in one cell, a cell for each a prefixed list what.?

Dear all, my apologies as I'm new to excel and the forum - I think in trying to make this simple, I've not specified enough, and thank you Martin and Deadlyduck for your comments and suggestions so far.

I have tried to be more specific with an updated speadsheet - hopefully with some clearer parameters

• I have 37 criteria against which I can score - I only put 10 in my spreadsheet for ease.
• The criteria feed in to a second tier of Scoring Areas of which there are 6 - I originally put only one example in my spreadsheet represented by my questions A&B - I have now put 6 Scoring Areas in the new attachment with comments to show how I want to Count and Sum
• The criteria I want to 'SUM' the scores from, will be pre-specified each time each of the six Scoring Areas
• In each Scoring Area (with it's pre defined list of Criteria it will be assessing) I want to count the number of those specified criteria that have attracted a score
• In each Scoring Area I was to add up the total score that was achieved for those pre defined criteria

Sorry if this seems backward - if I ought to be thinking a different way about this please let me know. I'm trying really hard to put what I mean into words but finding it difficult.

Regards
Richard

[martindwilson]

used named ranges. for each score area
so for score area 1
insert /name/define
create a name say area1
then in the refers to to box click each cell you to refer to seperate by comma
so it looks like this

=Sheet1!$B$3,Sheet1!$C$3,Sheet1!$D$3,Sheet1!$E$3,Sheet1!$G$3,Sheet1!$I$3
click ok
then use that name in the formula instead of cell references
i.e. =count(area1) in O2 and
=sum(area1) in 03

[RichardBewick]

Ahaa - this may have been so clear to you, but to me this is genius I was completely unaware of.

Many many thanks for your time

Richard