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!

 

[ausswe]

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))

Aha, ok. At first I got a #SPILL error so I moved the formula to the first row (3) and removed all previous data below in the column. This returned #CALC (Empty Arrays are not allowed):

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

 

[Fluff]

I'll get there in the end it needs to be wrapped in sum

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

 

[ausswe]

I'll get there in the end it needs to be wrapped in sum

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

That still returns #CALC! (Empty array), I tried using "Show Calculation Steps" but that only made Excel crash altogether

 

[Fluff]

That error suggests that you have nothing that matches all criteria.

 

[ausswe]

That error suggests that you have nothing that matches all criteria.

Thanks, I copied the formula down for the column and am realising that the calculations will take a very long time for this formula each time (the data that it has to go through is approx 100,000 rows of data).

Is it better/safer/faster to use something with SUMIFS and state the Names that should be matched in a named range instead?

 

[Fluff]

If you're copying the formula down 100,000 any formula is going to take time.

 

[ausswe]

If you're copying the formula down 100,000 any formula is going to take time.

No it's copied down approx 3500 rows, but the dataset it's calculating the sum from is approx. 100,000.

 

[Fluff]

In that case you can try using a sumifs instead & see how long that takes.

 

[Alex Blakenburg]

This is probably too close to Fluff's to be any faster but give it a try anyway:

=SUM(
(SoL[PID]=$A$7)
*(SoL[FromDate]>=DataME!$C$1)
*(SoL[ToDate]<=DataME!$D$1)
*(LET(aRow;--ISNUMBER(SEARCH(TRANSPOSE(Excl_Name);SoL[Name]));
BYROW(aRow;LAMBDA(r;SUM(r)=0)))))