What function in microsoft Excel is used to add a certain range of cells when condition is met

11.What function used to add a certain range of cells if a condition is met?A.ADDIFC. SUMIFB.TOTALIFD. PLUSIF

12.What features of MS Excel refers to the angle at which a text is displayed?

Get answer to your question and much more

13.What function used to count the cells with content in a range?

Get answer to your question and much more

14.Which among the following is the shortcut key for the Format Cells dialogbox?

Get answer to your question and much more

15.What syntax used in the AVERAGEIF function that includes the value orlabel that determine if the cell is part of the range to be average?A.CriteriaC. Logical TestB.RangeD. Average Range

16Additional ActivityActivity 6:Practice 101-Data Formula1.Type the data given below.2.Compare the profit, commission of the agent, andcompany’s income usingthe following formulas:Profit = sales–capital costAgent’s commission = sales * (4 %)Company’s income = profit –agent’s commission3.Copy the formula for the rest of the appliances using the different ways ofcopying formulas.4.Save your worksheet as DataFormula.ApplianceSalesCostProfitAgent’sComm(4%)Company’sIncomeVideocompact disk17, 35014, 800Television set(21”)16, 90014, 950Gas range7, 5005, 500Microwaveoven9, 0008, 500Refrigerator14, 00012, 500Computer28, 40024, 000Washingmachine7, 5006, 000

Get answer to your question and much more

17What I knowA. Multiple Choice1. B2. B3. B4. A5. D6. C7. B8. B9. A10. C11. C12. DAssessment:A. Multiple Choice1. B2. B3. B4. A5. D6. C7. B8. B9. A10. C11. C12. D

ReferencesBooksEmpowerment Technologies. Rex Book Store Inc. and Innovative Training Works,Inc., First Edition 2016Peñaflorida, A. R., Paquingan, A.R.,“Electronic Spreadsheet and DatabaseCreation and Management”, Vibal Publishing House, Inc.Web Sites“WPS Marketing, “5 Advanced Spreadsheet Skills You Need in Your Life”, 2018,accessedJanuary20,2021,-spreadsheet-skills-you-need-in-your-life/‘Excel 2016:“Getting Started with Excel”,GCFGlobal:GCFLearnFree.org., accessedJanuary 28, 2021, -started-with-excel/1/

For inquiries or feedback, please write or call:Department of Education–Schools Division of Negros OrientalKagawasan, Avenue, Daro, Dumaguete City, Negros OrientalTel #: (035) 225 2376 / 541 1117Email Address: [email protected]Website: lrmds.depednodis.net

End of preview. Want to read all 25 pages?

Upload your study docs or become a

Course Hero member to access this document

Professor

Anamae Tanguilig

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

How to use a logical AND or OR in a SUM+IF statement in Excel

• Article
• 05/05/2022
• 2 minutes to read
• Applies to:Microsoft Office Excel 2003, Microsoft Office Excel 2007, Excel 2010

In this article

Summary

In Microsoft Excel, when you use the logical functions AND and/or OR inside a SUM+IF statement to test a range for more than one condition, it may not work as expected. A nested IF statement provides this functionality; however, this article discusses a second, easier method that uses the following formulas.

For AND Conditions

=SUM(IF(Test1*Test2*...*Testn))

For OR Conditions

=SUM(IF(Test1+Test2+...+Testn))

More Information

Use a SUM+IF statement to count the number of cells in a range that pass a given test or to sum those values in a range for which corresponding values in another (or the same) range meet the specified criteria. This behaves similarly to the DSUM function in Microsoft Excel.

Example

This example counts the number of values in the range A1:A10 that fall between 1 and 10, inclusively.

To accomplish this, you can use the following nested IF statement:

=SUM(IF(A1:A10>=1,IF(A1:A10<=10,1,0)))

The following method also works and is much easier to read if you are conducting multiple tests:

=SUM(IF((A1:A10>=1)*(A1:A10<=10),1,0))

The following method counts the number of dates that fall between two given dates:

=SUM(IF((A1:A10>=DATEVALUE("1/10/99"))*(A1:A10<=DATEVALUE("2/10/99")),1,0))

Note

• You must enter these formulas as array formulas by pressing CTRL+SHIFT+ENTER simultaneously. On the Macintosh, press COMMAND+RETURN instead.
• Arrays cannot refer to entire columns.

With this method, you are multiplying the results of one logical test by another logical test to return TRUEs and FALSEs to the SUM function. You can equate these to:

TRUE*TRUE=1
TRUE*FALSE=0
FALSE*TRUE=0
FALSE*FALSE=0

The method shown above counts the number of cells in the range A1:A10 for which both tests evaluate to TRUE. To sum values in corresponding cells (for example, B1:B10), modify the formula as shown below:

=SUM(IF((A1:A10>=1)*(A1:A10<=10),B1:B10,0))

You can implement an OR in a SUM+IF statement similarly. To do this, modify the formula shown above by replacing the multiplication sign (*) with a plus sign (+). This gives the following generic formula:

=SUM(IF((Test1)+(Test2)+...+(Testn),1,0))

References

For more information about how to calculate a value based on a condition, click Microsoft Excel Help on the Help menu, type about calculating a value based on a condition in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

What function is used to add range?

How do you sum in Excel with if condition?

How do you create a range condition in Excel?

Which of the following Microsoft Excel functions can be used to add range of cells?