Method 5 – AVERAGEIF with ‘Not Equal to’ in Excel We have some data on salaries. Let’s find the average salary for both male and female employees. Steps Insert the following formula in Cell D12: =AVERAGEIF(C5:C10,"<>"&"Female",D5:D10) Press Enter. Use the following formula...
Method 6 – Applying the If Greater Than Operator with the SUMIF Function We want to sum scores that are greater than 60. Steps: In cell F6, insert the formula below: =SUMIF($C$5:$C$11,">"&60,$C$5:$C$11) Press Enter to find the total. Method 7 – Using the AVERAGEIF Fu...
3. AVERAGEIF =AVERAGEIF(range, criteria, [average_range]) What it does Returns the average (arithmetic mean) of all the cells in a range that meet a certain condition. Syntax AVERAGEIF(range, criteria, [average_range]) What the arguments mean range is the group of cells to be evaluated...
1. If you want to average for the Sales of Clothes Whose "Classification" equal "Women's clothing" and Sales are greater than 0. Double-click the cell E9, copy the formula =AVERAGEIFS(E2:E8,C2:C8,"Women's clothing",E2:E8,">0") to E9, press Enter, return to the average result ...
=SUMIFS(B1:B6, A1:A6, ">5", D1:D6, "<>Local Group") Returns 18: the sum of B1:B6 where A1:A6 is greater than 5 and D1:D6 is not equal to "Local Group" Calculate the mean of cells meeting conditions with AVERAGEIF() and AVERAGEIFS() =AVERAGEIF(A1:A6, ">5") Returns ...
Now let’s look into a few functions that are likely to return the #DIV/0 error if misapplied. AVERAGE Function What is the average function about? = Sum of numbers / Count of those numbers As the function primarily involves the division, it is likely to return a #DIV/0! Error if th...
Handle Excel DIV/0 error with IF formula To mask only Div/0 errors in Excel, use anIF formulathat checks if the divisor is equal (or not equal) to zero. For example: =IF(B2=0,"",A2/B2) Or =IF(B2<>0,A2/B2,"") If the divisor is any number other than zero, the formulas ...
It checks if H4 meets or exceeds $5000 and displays messages based on that condition. Find the average of numbers in range B2:B10 where corresponding cells in range A2:A10 meet a specific criteria (e.g., equal to “Apple”): Use=AVERAGEIF(A2:A10, “Apple”, B2:B10). ...
AVERAGE Function AVERAGEA Function AVERAGEIF Function AVERAGEIFS Function BETA.DIST Function BETA.INV Function BINOM.DIST Function BINOM.DIST.RANGE Function BINOM.INV Function CHISQ.DIST Function CHISQ.DIST.RT Function CHISQ.INV Function CHISQ.INV.RT Function ...
=SUMIF(A2:A10, "<>apples", B2:B10) When the criterion is in another cell, concatenate the "not equal to" operator and a cell reference like this: =SUMIF(A2:A10, "<>"&F1, B2:B10) SUM IF blank This example shows how to sum cells in one column if a corresponding cell in anothe...