Criteria >700 and <1000

[Brian]

Hello,

I have the following data.
COL C COL I
600 50
800 35
950 45
805 90
1200 22
1355 12

I triede using the following formula do SUM the values in
COL I if the value in COL C was between 700 and 1000.
=SUMIF(C83:C102,">700 <1000",I83:I102)
But it's not working.

Any help would be greatly appreciated.

Brian

 

[Frank Kabel]

Hi Brian
SUMIF only accepts one condition try
=SUMPRODUCT((C83:C102>700)*(C83:C102<1000),I83:I102)

or with an alternative syntax:
=SUMPRODUCT(--(C83:C102>700),--(C83:C102<1000),I83:I102)

Or using two SUMIF functions in your case
=SUMIF(C83:C102,">700",I83:I102)-SUMIF(C83:C102,">=1000",I83:I102)

 

[Brian]

Many thanks.

This worked well.

Would it be possible to get the AVERAGE of the values in
COL I when they meet the same conditions?

Max

 

[Frank Kabel]

Hi Brian
some ways:
1. using SUMPRODUCT:
=SUMPRODUCT(--(C83:C102>700),--(C83:C102<1000),I83:I102)/=SUMPRODUCT(--
(C83:C102>700),--(C83:C102<1000))

2. Using an array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF((C83:C102>700)*(C83:C102<1000),I83:I102))

 

[Norman Harker]

Hi Brian!

Use:
SUMPRODUCT(--($C$83:$C$102>700),--($C$83:$C$102<1000),$I$83:$I$102)/SUMPRODUCT(--($C$83:$C$102>700),--($C$83:$C$102<1000))

Rather than hard coding your bounds, it is better to put them in cells
and to refer to those cells. Then with not too much variation you can
change them or create a table; talking of which, you might investigate
the use of Pivot Tables.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Brian]

Sorry to be bothering you again.
I thought I could figure out on my on, but I guess not.
My data also as date:
COL A COL B
12-Feb-02 54
13-Feb-02 35
14-Feb-02 -22
15-Feb-04 5
16-Feb-04 120
etc etc

The columns go for about 5 years. How can I get a
average of COL B per DAY. Average for MONDAYS,TUESDAYS,
etc.

Once again, many thanks.

Brian

 

[Frank Kabel]

Hi
one way:
=SUMPRODUCT(--(WORKDAY(A1:A100)=2),B1:B100)/SUMPRODUCT(--(WORKDAY(A1:A1
00)=2))

our you may use a pivot table and group the data by day