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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
521
Office Version
  1. 2016
Platform
  1. Windows
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
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
521
Office Version
  1. 2016
Platform
  1. Windows
Oops, sorry it is not the formula you need , now i understood better.
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Forum statistics

Threads
1,171,041
Messages
5,873,439
Members
432,980
Latest member
KMorrison12345

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