Excel SumIfs with a named range

[Groovy Chick]

Hi, I am trying to sum a figure for graphs using a named range so that I can change the week no and it automatically updates the formula and, therefore, the graph. The workings out on the Graph Worksheet are looking at Weekly QN Details worksheet and totalling column J if days in column M is greater than 75 and then use the value in the look up in T2 - week 1, week 2 and so on. I have formula <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!T2,WeekNos)> which I know is wrong as returning value! Should I be using a sumif?

 

[Groovy Chick]

Hi Steve, I did that but get a zero amount back when should be around £650k - is this formula wrong? <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!N:N,T2)> T2 contains my data validation dropdown and is currently set at week 6.Sorry again.

 

[steve the fish]

No that formula is fine. J contains values to sum, M is a condition of the sum (>75) and N another condition (eg Week 6). Do the whole thing via a filter applied to the range. Apply these formulae to the first cell in the filtered range (change the row number to suit). Check these are true:

=ISNUMBER(J2)
=ISNUMBER(M2)
=N2=$T$2

Copy them down. All filtered results should show as true.

 

[Groovy Chick]

Steve, sorry I don't understand. Which columns am I filtering and where do I put the formulae you listed? Rather confused as to what I put where and what range I'm filtering?

 

[steve the fish]

Ok go to your data sheet. Weekly QN Details. Apply a filter on the headers. In column M filter any value greater than 75. Then in column N filter for equals say "Week 6". You now have a filtered range. In spare cells to the right of your data place the formulas. Change the 2 in J2 to be the same as the first row number of the filtered range.

 

[Groovy Chick]

OK, =N2=$T$2 is returning false. Is that to do with the formatting and reason for zero result?

 

[steve the fish]

T2 needs the sheet name if its not in the same sheet so check that first

 

[Groovy Chick]

I did that like this <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!N:N,'Weekly QN Details'!,T2)> and got an error?

 

[steve the fish]

You have missed the range at the end:

'Weekly QN Details'!,T2)

 

[Groovy Chick]

Sorry, do I put that after the 'Weekly QN Details'!,T2 that's already in the formula?

 

[steve the fish]

Ok lets start again. Find a row in your data that you think satisfies your two conditions. Copy and paste here an exact copy of the line removing anything sensitive.