SUMIFS exclude value if partial match in list/named range

[ausswe]

Hi,
I've been playing around with SUMIFS to get a formula that sums multiple criteria but also excludes some values if found as a partial match in a named range.
I have added partial text strings in a list with the named range "Excl_Name" and am trying to get SUMIFS to sum all matches in column SoL[Name] unless there is a parital match in the list Excl_Name but so far I haven't been able to get it to work.

I have managed to get it to work with wildcard and one specific criteria but not for a whole list and I haven't been able to see a solution when looking around online.

Can this be done by using SUMIFS? (I have also tried with SUMPRODUCT, but I have read that SUMIFS will be quicker to calculate - seeing that the dataset is very large.

So far I have (but doesn't work:

=SUMIFS(SoL[Hours];SoL[PID];$A7;SoL[FromDate];">="&DataME!$C$1;SoL[ToDate];"<="&DataME!$D$1;SoL[Name];"<>Exkl_Name")

I have also tried the following which results in #SPILL:

=SUMIFS(SoL[Hours];SoL[PID];$A7;SoL[FromDate];">="&DataME!$C$1;SoL[ToDate];"<="&DataME!$D$1;SoL[Name];"<>*&Exkl_Name&"*")

I would appreciate any thoughts on this.

Thanks!

 

[Fluff]

Is the named range for the exclusions Excl_Name or Exkl_Name?

 

[ausswe]

Is the named range for the exclusions Excl_Name or Exkl_Name?

Sorry Lost in translation from Swedish to English... Excl_Name it should be in the formula as well.

 

[Fluff]

In that case try

=Sum(SUMIFS(SoL[Hours];SoL[PID];$A7;SoL[FromDate];">="&DataME!$C$1;SoL[ToDate];"<="&DataME!$D$1;SoL[Name];"<>*"&Excl_Name&"*"))

 

[ausswe]

In that case try

=Sum(SUMIFS(SoL[Hours];SoL[PID];$A7;SoL[FromDate];">="&DataME!$C$1;SoL[ToDate];"<="&DataME!$D$1;SoL[Name];"<>*"&Excl_Name&"*"))

Not quite - I can see that something is deducted from the hours when there is a partial match in SoL[Name] from the Named Range - however the total hours calculated is 6 times higher then what it should be (in the data total number of hours are 109,81 hours - matching the date range and excluding one Name from the named range. However the sum calculated by the formula is 658,86 (and 658,86 / 109,81 = 6)

 

[ausswe]

Not quite - I can see that something is deducted from the hours when there is a partial match in SoL[Name] from the Named Range - however the total hours calculated is 6 times higher then what it should be (in the data total number of hours are 109,81 hours - matching the date range and excluding one Name from the named range. However the sum calculated by the formula is 658,86 (and 658,86 / 109,81 = 6)

Noticing now that if I add values to the named range (with entries that should be excluded) the total sum calculated increases by the same number of entries (earlier I had 6 entries, now I added two test rows in the named range and the total sum calculated increase).

 

[Fluff]

Oops, as you are using <> it needs to be an AND condition, not an OR condition. Which means with sumifs you would need to put each cell in the named range as a separate criteria.

 

[Fluff]

Try

 =FILTER(SoL[Hours];(SoL[PID]=$A7)*(SoL[FromDate]>=DataME!$C$1)*(SoL[ToDate]<=DataME!$D$1)*(MAP(SoL[Name];LAMBDA(m;SUM(IFERROR(SEARCH(Excl_Name;m)))))=0))

 

[ausswe]

Try

 =FILTER(SoL[Hours];(SoL[PID]=$A7)*(SoL[FromDate]>=DataME!$C$1)*(SoL[ToDate]<=DataME!$D$1)*(MAP(SoL[Name];LAMBDA(m;SUM(IFERROR(SEARCH(Excl_Name;m)))))=0))

That returns the error: You've entered too few arguments for this function. The cursor jumps to ";m)"

 

[Fluff]

Missed a bit from the iferror

 =FILTER(SoL[Hours];(SoL[PID]=$A7)*(SoL[FromDate]>=DataME!$C$1)*(SoL[ToDate]<=DataME!$D$1)*(MAP(SoL[Name];LAMBDA(m;SUM(IFERROR(SEARCH(Excl_Name;m);0))))=0))