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
1	201801	50		2018	350
2	201802	60
3	201803	70
4	201804	80
5	201805	90
6	201901	50
7	201902	60
8	201903	70

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E1 =SUMPRODUCT((LEFT(A1:A8,4)=$D$1)*B1:B8) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

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