# sumifs if criteria range is text format



## ExcelNewbie2020 (Dec 12, 2022)

Would it be possible to sum a number within range if the criteria range is in text format.  See table

(All months are in text format)

code​janfebmaraprmayjunjulaugsepoctnovdec3100​200​100​100​100​100​100​100​100​100​100​100​1100​100​100​100​100​100​100​100​100​100​100​100​2100​200​200​200​200​200​200​200​200​200​200​200​2100​4100​3100​200​100​100​100​100​100​100​100​100​100​100​4100​4100​100​100​100​100​100​100​100​100​100​100​100​2100​5100​200​200​200​200​200​200​200​200​200​200​200​code​3​from​febend​julyResult1400​


----------



## jasonb75 (Dec 12, 2022)

Here's one way that works.

```
=SUMPRODUCT(INDEX(B2:M11,,MATCH(C16,B1:M1,0)):INDEX(B2:M11,,MATCH(C17,B1:M1,0))*(A2:A11=C15))
```


----------



## ExcelNewbie2020 (Dec 12, 2022)

jasonb75 said:


> Here's one way that works.
> 
> ```
> =SUMPRODUCT(INDEX(B2:M11,,MATCH(C16,B1:M1,0)):INDEX(B2:M11,,MATCH(C17,B1:M1,0))*(A2:A11=C15))
> ```


thank you.. but it returns #N/A


----------



## jasonb75 (Dec 12, 2022)

That's because you have July in C17 instead of Jul


----------



## ExcelNewbie2020 (Dec 12, 2022)

ExcelNewbie2020 said:


> thank you.. but it returns #N/A
> 
> View attachment 80698


ohhh.. good catch.. thanks man..its working fine...


----------



## Sufiyan97 (Dec 12, 2022)

Hi @jasonb75 can you please explain below part of the formula:

=SUMPRODUCT(INDEX(B2:M11,,MATCH(C16,B1:M1,0))*:INDEX(B2:M11,,MATCH(C17,B1:M1,0))**(A2:A11=C15))


----------



## jasonb75 (Dec 15, 2022)

Sufiyan97 said:


> can you please explain below part of the formula


It produces the end part of the sumproduct range, if you copy the sample data to excel then evaluate the formula you will be able to follow it.


----------



## shinigamilight (Dec 15, 2022)

Alternative solution

C18:    =SUMPRODUCT(--(DATEVALUE(1&C17)>=DATEVALUE(1&B1:M1))*--(DATEVALUE(1&C16)<=DATEVALUE(1&B1:M1))*--(A2:A11=C15)*B2:M11)


----------



## jasonb75 (Dec 16, 2022)

shinigamilight said:


> Alternative solution


It's actually an 'Alternative suggestion' 

The strings that you're forming with the datevalue function are region specific so will not work for everyone without changing them.


----------

