SUMIF meets a criteria

[TimR]

I am trying to sum if the criteria is between two values;

SUMIF(C2:C834,(>=8000-0010,<=8000-0510),J2:J834)

I keep getting error messages. The data is formatted as the attached (hopefully). In the example I point to the values 8000-0010 and 8000- 0510.

Any help would be appreciated.

Tim

[Peo Sjoblom]

1. You cannot use 2 conditions in a SUMIF
2. The values you have are text (8000-0010 is not a numeric)
so how can you apply a greater than etc condition to it

I'd suggest you split the number into 2 like

8000 in one column and 0010 in another

then you can use

=SUMPRODUCT(--(RANGE1=8000),--(Range2>=10),--(Range2<=510),J2:J834)

otherwise you need to parse out the different parts of the string
and it becomes fairly complicated


--

Regards,

Peo Sjoblom



"TimR" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to sum if the criteria is between two values;
>
> SUMIF(C2:C834,(>=8000-0010,<=8000-0510),J2:J834)
>
> I keep getting error messages. The data is formatted as the attached
> (hopefully). In the example I point to the values 8000-0010 and 8000-
> 0510.
>
> Any help would be appreciated.
>
> Tim
>
>
> --
> TimR
> ------------------------------------------------------------------------
> TimR's Profile:
http://www.excelforum.com/member.php...o&userid=27843
> View this thread: http://www.excelforum.com/showthread...hreadid=478878
>

[Bob Phillips]

SUMPRODUCT(--(C2:C834>="8000-0010"),--(C2:C834<="8000-0510"),J2:J834)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"TimR" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to sum if the criteria is between two values;
>
> SUMIF(C2:C834,(>=8000-0010,<=8000-0510),J2:J834)
>
> I keep getting error messages. The data is formatted as the attached
> (hopefully). In the example I point to the values 8000-0010 and 8000-
> 0510.
>
> Any help would be appreciated.
>
> Tim
>
>
> --
> TimR
> ------------------------------------------------------------------------
> TimR's Profile:
http://www.excelforum.com/member.php...o&userid=27843
> View this thread: http://www.excelforum.com/showthread...hreadid=478878
>