Wild Card for SUMIF criteria
 

I am trying to sum data in column M if the criteria in column F is met. The
criteria in F consists of 2 to 8 didget numbers such as 20, 2010, 20201, etc.
I want to sum any value in M when the criteria F is anything starting with
"20". Something to the effect of;

SUMIF(F:F,20*,M:M)

and I can not reduce the criteria in F down to 2 digets.

Thanks for any help.

Ronbo

Wild Card for SUMIF criteria
 

You cannot use wildcards for numbers, only text

=SUMPRODUCT(--(LEFT(F1:F10000,2)="20"),M1:M10000)

works

note that you cannot use SUMPRODUCT with the whole column unless you have
Excel 2007 so you need to specify the range you are testing


--


Regards,


Peo Sjoblom


"Ronbo" wrote in message
...
I am trying to sum data in column M if the criteria in column F is met. The
criteria in F consists of 2 to 8 didget numbers such as 20, 2010, 20201,
etc.
I want to sum any value in M when the criteria F is anything starting with
"20". Something to the effect of;

SUMIF(F:F,20*,M:M)

and I can not reduce the criteria in F down to 2 digets.

Thanks for any help.

Ronbo

Wild Card for SUMIF criteria
 

Thanks a lot for the solution and the information.


"Peo Sjoblom" wrote:

You cannot use wildcards for numbers, only text

=SUMPRODUCT(--(LEFT(F1:F10000,2)="20"),M1:M10000)

works

note that you cannot use SUMPRODUCT with the whole column unless you have
Excel 2007 so you need to specify the range you are testing


--


Regards,


Peo Sjoblom


"Ronbo" wrote in message
...
I am trying to sum data in column M if the criteria in column F is met. The
criteria in F consists of 2 to 8 didget numbers such as 20, 2010, 20201,
etc.
I want to sum any value in M when the criteria F is anything starting with
"20". Something to the effect of;

SUMIF(F:F,20*,M:M)

and I can not reduce the criteria in F down to 2 digets.

Thanks for any help.

Ronbo