SUMIF Function

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
Can multiple criteria be used with this function? I am using this function to find an average time based on the criteria being greater than a certain number and less than or equal to another cetain number.

Is there another function available to accomplish this?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
Hi ,

try average(if(and(logicalone;logical2);your range)) entered as an array with Ctrl, shift and enter

G.Luck
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67

ADVERTISEMENT

gaftalik,

Here is the formula you suggested.

=AVERAGE(IF(AND(K:K>&A59;K:K<=&A60);K:K))

It tells me that there is an error in my formula. I tried entering as an array, no luck. Column K contains times in [H]:MM format. I am trying to sum all the times within a range of time (two criteria), and then find the average.
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
Oops, sorry it is not the formula you need , now i understood better.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Enter formula with Ctrl + Shift + Enter
Book1
ABCD
1CriteriaTime
211
322
433
5443.5
655
766
877
Sheet5
 

BCS2

Board Regular
Joined
Dec 8, 2003
Messages
67
fairwinds:

This is the formula that you suggested.

{=AVERAGE(IF((K3:K62>$A59)*(K3:K62<=$A60),K3:K62))}

It seems to work so far. However, the amount of data that is entered into this sheet each week varies greatly. Instead of specifying a data range (K3:K62) I would like to include the entire K column. That way, when the data amount varies I won't have to change the formula every week.

Possible?

:pray:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
The formula don't like the whole column i.e. A:A but you can specify a range as large as you like as AVERAGE disregards text and empty cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top