Sumifs Wild Character

[vichisov]

Hello,

I have column A with years 2014, 2013, 2012 and column B with numeric values. Lets say there are 25 rows like that. Have a sumif that aggregates based on a dropdown (2012, 2013, 2014, ***). Everything works fine, until *** is selected which then does not act as a wild character. Whats the problem and how do I fix it?

Thanks,

[FDibbins]

It wont work that way, because it is looking to criteria that actually are ***

Try this approach instead. Include an "all" in the dd list, and modify the calc like this...

	A	B	C	D
2	2012	1	all	28
3	2012	2
4	2012	3
5	2013	4
6	2013	5
7	2014	6
8	2014	7

C2=DD list containing 2012,2013,2014,all
D2=IF(C2="all",SUM($B$2:$B$8),SUMIF($A$2:$A$8,C2,B2:B8))

[sandy666]

or add apostrophe before each year in year range, will be treated as text

[vichisov]

Of course I used that cell in a countif that works with wild characters. My solution ended up being to convert the year to text and for some reason that does the trick. Where those fields are actually used as numbers, I multiply them by 1 before using them.

[vichisov]

Just saw that the poster above came up with the same solution as I did.

What is the rule at play here? I got it to work, but not sure why strings and numbers are treated differently in a sumif.

[sandy666]

"abc" => ISTEXT = TRUE
"123" => ISTEXT = TRUE
123 => ISTEXT = FALSE , ISNUMBER = TRUE
* => ISTEXT = TRUE