SUMIFS multiple criteria including number "starts with"

[powercell99]

Having a problem getting this sumifs formula to work correctly using a wild card.

Sum Range: Col I
Criteria Range 1: Col C
Criteria 1: Starts with "10" (there is a 6 digit number - not a number stored as text, 1st 2 digits should be 1 and 0)
Criteria Range 2: Col N
Criteria 2: = C$1


=SUMIFS($I:$I, $C:$C, "10*", $N:$N, C$1)

I'm getting zero values as a result of the above formula.

Anybody know what i'm doing wrong? or is there another approach to this like --sumproduct() or another function??

Any help would be greatly appreciated.

 

[arthurbr]

Perhaps =SUMPRODUCT($I:$I,--(LEFT($C:$C,2)="10"),$N:$N=C$1)

Sumproduct will not work on entire columns in pre 2007 versions ( and I think it's usually a bad habit)

 

[powercell99]

Hey Arthur, thanks for the quick response. I know what you mean sumproduct as a bad habit, i use it on an as needed basis too.

I tried SUMPRODUCT($I:$I,--(LEFT($C:$C,2)="10"),$N:$N=C$1), and still get zero, also tried SUMPRODUCT($I:$I,--(LEFT($C:$C,2)=10),$N:$N=C$1), and get zero. Also tried specific range not entire column, but got nothing. I have office 365. I'm really stumped why none of these will work. Any other suggestions?

Thanks

 

[arthurbr]

I didn't mean SUMPRODUCT is a bad habit but using entire columns is ( in my opinion)

And like this =SUMPRODUCT(--(LEFT($C:$C,2)="10")*$N:$N=C$1*$I:$I)

 

[powercell99]

still nothing. I get a #Value ! result. I may just create a helper column for the 1st two characters and then do a normal sumifs. Does anyone else have any other suggestions that wouldnt require a helper column?

 

[arthurbr]

Or =SUMPRODUCT(--(LEFT($C:$C,2)="10")*--($N:$N=C$1)*$I:$I)

Is this condition $N:$N=C$1 correct?

 

[Special-K99]

Try

=SUMPRODUCT((C1:C10000>=100000)*(C1:C10000<199999)*(N1:N10000=C1))

If you get #VALUE it usually means you have text within one of the ranges.
SUMPRODUCT can't handle a range that consists of numbers and text, you need to clean the data up first.

 

[powercell99]

Yes $N:$N=C$1 is correct. I actually have it referencing two sheets, Columns N:N, C:C, and I:I are on Sheet 1, and cell $C$1 on Sheet 2. N:N on Sheet 1 is a pay period number, that should reflect the value on $C$1 on sheet 2. C:C on Sheet 1 is an account number, and i want to sum the accounts that start with "10" for the pay period listed on $C$1 on Sheet 2. I:I on Sheet 1 is the $$ i want summed. so the actual formula is:

SUMPRODUCT(--(LEFT(Sheet1!$C:$C,2)="10")*--(Sheet1!$N:$N=Sheet2!C$1)*Sheet1!$I:$I)

Makes sense? I still get a #value from the above result.

 

[TWriter1]

Did you try auditing the formula to see where the issue is?