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, below is headers and a row of sample data that meets conditions (sorry, my system seems to restrict how I can paste this data):Notification Notifictn type Material Description Ref. quantity Quarantine Location Plant for mat. Date In Quarantine Part Number Single Value Part Number Value with Complaint QTY Owner/ Action Analysis Date Days Week Number123456 RA ABCD A SAMPLE 13 1234 A20 20/06/2016 £50.00 £650.00 SD To Advise 08/01/2018 558 Week 6

 

[steve the fish]

So the formula you had:

=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!N:N,T2)

should work if cell T2 in the sheet where this formula is housed contains Week 6. If it produces 0 then check for any trailing spaces in the data. You also need to make sure that the cells with the £ signs are just formatted to show the £ signs. To check click on them and the £ sign should not appear in the formula bar just the value itself.

 

[steve the fish]

Does this produce a result?

=SUMPRODUCT(0+'Weekly QN Details'!$J$2:$J$1000,--(TRIM('Weekly QN Details'!$M$2:$M$1000)>75),--(TRIM('Weekly QN Details'!$N$2:$N$1000)=TRIM($T$2)))

 

[Groovy Chick]

Hi Steve, I tried the worksheet reference again in front of T2 and it worked - <=SUMIFS('Weekly QN Details'!J:J,'Weekly QN Details'!M:M,">75",'Weekly QN Details'!N:N,'Weekly QN Details'!T2)>The mysteries of Excel! Weird.Thank you so much for your help, very much appreciated.

 

[steve the fish]

If you use the formula on a seperate sheet to the one where you are refering to T2 then you need a sheet reference or it will use T2 of the sheet where the formula is housed.

 

[Groovy Chick]

Yes, and you had said that already but when I tried it before it didn't work. Anyway, thank you again.