Sumif Using Wildcard

[cunningAce]

Hi,

I'm am trying to perform a sum if on the below data.
I want to sum only the numbers that begin with the year 2018 (or what ever selection I chose) so I tried the following but it returns zero?

=SUMIF(A1:A8,"2018*",B1:B8)

Also tried with 2018 typed in cell G1

=SUMIF(A1:A8,G1&"*",B1:B8)


Column A Colum B
201801 50
201802 60
201803 70
201804 80
201805 90
201901 50
201902 60
201903 70

Any help greatly appreciated.

 

[Joe4]

Try this:

=SUMPRODUCT(--(LEFT(A1:A8,4)="2018"),B1:B8)

 

[jim may]

Excel 2010

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]201801[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]350[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]201802[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]201803[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]201804[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]201805[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]201901[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]201902[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]201903[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUMPRODUCT((LEFT(A1:A8,4)=$D$1)*B1:B8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[cunningAce]

Works great, thank you

 

[cunningAce]

=SUMPRODUCT(​

(LEFT(A1:A8,4)=$D$1)*B1:B8​

)​

returns 0

needed to turn the string back to number by *1 to get it to work.

Thanks

 

[Peter_SSs]

Assuming column A is year and month, you could also use SUMIFS

=SUMIFS(B2:B9,A2:A9,">201800",A2:A9,"<201813")
or
=SUMIFS(B2:B9,A2:A9,">"&G1*100,A2:A9,"<"&G1&13)

 

[jim may]

Your cell $D$1 must contain the text value of 2018, by using the apostrophe ' before the 2018, like '2018, since the Left() function converts its object into text.
Does that help?

 

[cunningAce]

Try this:

=SUMPRODUCT(--(LEFT(A1:A8,4)="2018"),B1:B8)

So I have gone with this formula, thanks all.

How would I add a further condition?
So it would only sum values that start with 2018 (as does currently) but then also sums only values that have "Q1" in column C

Much appreciated

 

[Peter_SSs]

How would I add a further condition?
So it would only sum values that start with 2018 (as does currently) but then also sums only values that have "Q1" in column C

For my suggestion, the alteration would be

=SUMIFS(B2:B9,A2:A9,">201800",A2:A9,"<201813",C2:C9,"Q1")

Edit: Looking back to post #1 , my row ranges should be 1:8 not 2:9. Same in my previous post.

 

[Joe4]

Just keep adding conditions, i.e.

=SUMPRODUCT(--(LEFT(A1:A8,4)="2018"),[COLOR=#ff0000]--(C1:C8="Q1")[/COLOR],B1:B8)