Trying to nest MID funciton within SUMIFS

[AGibo]

Hi there,
Can someone please tell me what is wrong with this syntax??
Nesting the MID function seems to throw it out.

=SUMIFS(Bring_in!D:D,MID(Bring_in!N:N,4,4),A$3)

 

[AGibo]

The data in Column N is in format 10.1020.00000.50001 through to 10.1020.00000.50009 A$3 is 50001. So we want to sum values where the last quad of Column N is 50001.

 

[AGibo]

And Column D are the values that we will be summing.

 

[barry houdini]

Hello AGibo, you can't modify a range in SUMIFS with a function like MID, try turning that round to use wildcards.

From your initial formula that looked like A3 contains the 1020 part (4 characters from character 4) but if A3 = 50001 then try

=SUMIFS(Bring_in!D:D,Bring_in!N:N,"*"&A$3)

 

[Norie]

Use a wildcard.

=SUMIFS(D:D,N:N, "*"&A3)

PS Doesn't 50001 have 5 digits? To extract that you can use =RIGHT(N1, 5)

 

[AGibo]

Thanks all. Using the wildcards has solved my issue. And worth saying thanks again for the more than prompt replies I have received.

Hoping I can return the favour to other users one day,

 

[T. Valko]

Hi there,
Can someone please tell me what is wrong with this syntax??
Nesting the MID function seems to throw it out.

=SUMIFS(Bring_in!D:D,MID(Bring_in!N:N,4,4),A$3)

Since there is just a single condition you can use SUMIF.

=SUMIF(Bring_in!N:N,"*"&A$3,Bring_in!D:D)