SUMIFS and wildcards

[coake]

Hey folks,
Just looking for some advise on how to fix this.

Problem: Double counting because of the wildcard.
What I'm trying to do is sum all 11.20 for the month of X and sum all of 11.2 for the month of X

Data:

3-Jun-18 <tbody> </tbody> 180433 FN - 11.20 HRV First Nation Medic <tbody> </tbody> $150.00 <tbody> </tbody> 01-Jul-18 <tbody> </tbody> 180433 FN - 11.20 HRV First Nation Medic <tbody> </tbody> $750.00 <tbody> </tbody> 01-Jul-18 <tbody> </tbody> 180433 FN - 11.20 HRV First Nation Medic <tbody> </tbody> $750.00 <tbody> </tbody> 02-Jul-18 <tbody> </tbody> 180433 FN - 11.20 HRV First Nation Medic <tbody> </tbody> $750.00 <tbody> </tbody> 02-Jul-18 <tbody> </tbody> 180433 FN - 11.20 HRV First Nation Medic <tbody> </tbody> $750.00 <tbody> </tbody> 02-Jul-18 <tbody> </tbody> 180433 33445 FN - 11.2 Mc Lk FNation Medic <tbody> </tbody> $150.00 <tbody> </tbody> 02-Jul-18 <tbody> </tbody> 180433 33445 FN - 11.2 Mc Lk FNation Medic <tbody> </tbody> $75.00 <tbody> </tbody> 3-Jun-18 <tbody> </tbody> 180433 33445 FN - 11.2 Mc Lk FNation Medic <tbody> </tbody> $150.00 <tbody> </tbody> 3-Jun-18 <tbody> </tbody> 180433 33445 FN - 11.2 Mc Lk FNation Medic <tbody> </tbody> $75.00 <tbody> </tbody> 7-Jun-18 <tbody> </tbody> 180433 33445 FN - 11.2 Mc Lk FNation Medic <tbody> </tbody> $150.00 <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

My formula is:
=SUMIFS(DATA!K:K,DATA!D:D,">="&DATE(2018,7,1),DATA!D:D,"<="&DATE(2018,7,31),DATA!E:E,"*" & A97 & "*")

This formula sums both the 11.2 and the 11.20
Also, the number I'm focusing on (11.20) is not always at position 13

Any suggestions?

Cliff

 

[KennyGreens]

What is in cell A97?

 

[coake]

It contains my search criteria so it has the 11.2, or 11.20, or 2.1 or 2.10 etc

 

[Marcelo Branco]

Maybe...

=SUMIFS(DATA!K:K,DATA!D:D,">="&DATE(2018,7,1),DATA!D:D,"<="&DATE(2018,7,31),DATA!E:E,"* "&A97&" *")

Note the spaces: "* " and " *"

M.

 

[coake]

Awesome thanks Marcelo. The leading space doesn't affect it, but the 2nd " *" will pull the single numbers perfectly.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.