SUMIFS and wildcards

coake

New Member
Joined
Aug 23, 2017
Messages
34
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

coake

New Member
Joined
Aug 23, 2017
Messages
34
It contains my search criteria so it has the 11.2, or 11.20, or 2.1 or 2.10 etc
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

New Member
Joined
Aug 23, 2017
Messages
34
Awesome thanks Marcelo. The leading space doesn't affect it, but the 2nd " *" will pull the single numbers perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top