Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Вивчайте SUMIFS, COUNTIFS and AVERAGEIFS | Logical Functions
Google Spreadsheets
course content

Зміст курсу

Google Spreadsheets

Google Spreadsheets

1. Introduction to Google Sheets
Why do you need Google Sheets?Creating Google SheetsOpening Google SheetsRenaming Google SheetsCopying Google SheetsDeleting Google SheetsMoving Google SheetsDownloading Google Sheets
2. Formatting of Cells and Ranges
Basic Editing OperationsFormatting DataWorking with FontsFormatting Text and Filling with ColorFormatting BordersMerging Cells and Text AlignmentClear Formatting and Paint Format ToolsSelecting Cells and RangesAdjusting Column and Row Size and PositionCopying and Deleting Columns and Rows Freezing and Grouping Rows and Columns
3. Advanced Formatting of Cells
Splitting Text to ColumnsTrimming WhitespacesInserting Internal and External HyperlinksAutocompleting CellsCreating CheckboxesCreating FormsAdding NotesAdding ImagesCreating DrawingsChallenge: Mastering Event Planning ChallengeChallenge: Your Event, Your Plan
4. Sorting, Filtering and Cleaning Data
Find and ReplaceSorting RangeSearching Filter and Filter by ConditionCustom Filter ViewsConditional Formatting and Color Scale Using SlicersNamed Range Removing DuplicatesChallenge: Library Book List Management
5. Creating Charts
Line ChartBasic Chart SettingsChart Style and Chart and Axis TitlesLegend, Gridlines and TicksHorizontal and Vertical Axis CustomizationArea ChartColumns and Stacked ColumnsPie ChartChallenge: Data Visualization Sprint Sales Insights
6. Basic Functions
Basic CalculationsArithmetic Functions Statistical FunctionsRounding FunctionsWorking with Numbers and Integers Working with Roots and PowersRandom Number GenerationText Functions Working with Dates and TimeSort FunctionChallenge: Sales Visualization Trends and Analytics
7. Logical Functions
AND and OR FunctionsIF and Nested IF FunctionsIFS FunctionSUMIFS, COUNTIFS and AVERAGEIFSChallenge: Employee Performance and Payroll Analysis with Excel Functions
8. Collaboration and Security
Access Control and Real-Time Document CollaborationNotification Rules Chatting with Other UsersProtecting Sheets and RangesComments and Google Meet IntegrationVersion History and Data Recovery

book
SUMIFS, COUNTIFS and AVERAGEIFS

In this chapter we will explore the SUMIFS, COUNTIFS, and AVERAGEIFS functions. These functions are designed to perform calculations based on multiple specified conditions. They enable you to process large datasets by focusing on values that meet several criteria.

SUMIFS Function

The SUMIFS function sums values in a range that meet multiple specified conditions.

Syntax

SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2,criterion2,...criteria\_range2, criterion2, ...)

  • sum_range – The range to sum;
  • criteria_range1 – The range to test against criterion1;
  • criterion1 – The condition applied to criteria_range1;
  • criteria_range2, criterion2, ... – OPTIONAL – Additional ranges and conditions to test.

COUNTIFS Function

The COUNTIFS function counts the number of cells in a range that meet multiple specified conditions.

Syntax

COUNTIFS(criteria_range1, criterion1, criteria_range2,criterion2,...criteria\_range2, criterion2, ...)

  • criteria_range1 – The range to test against criterion1;
  • criterion1 – The condition applied to criteria_range1;
  • criteria_range2, criterion2, ... – OPTIONAL – Additional ranges and conditions to test.

AVERAGEIFS Function

The AVERAGEIFS function calculates the average of values in a range that meet multiple specified conditions.

Syntax

AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2,criterion2,...criteria\_range2, criterion2, ...)

  • average_range – The range to average;
  • criteria_range1 – The range to test against criterion1;
  • criterion1 – The condition applied to criteria_range1;
  • criteria_range2, criterion2, ... – OPTIONAL – Additional ranges and conditions to test.

Examples for SUMIFS, COUNTIFS, and AVERAGEIFS

Value (A)Value (B)Value (C)FormulaResultComment
Green1302023-04-22SUMIF(A2:A14, "Green", B2:B14)560Sums values from column B where column A equals "Green." Total 6 values: 130 + 60 + 90 + 110 + 95 + 75.
Red1502022-10-15SUMIFS(B2:B14, A2:A14, "Red", B2:B14, ">90")250Sums values from column B where column A equals "Red" and column B values are greater than 90. Matches 2 values: 150 + 100.
Blue1202024-02-09SUMIFS(B2:B14, C2:C14, ">=2023-01-01", C2:C14, "<=2023-12-31")460Sums values from column B where dates in column C are in 2023. Matches values: 130, 150, 120, 60, 90.
Green602022-06-17COUNTIF(A2:A14, "Green")6Counts cells in column A equal to "Green." Total 6 matches.
Green902023-11-05COUNTIFS(A2:A14, "Red", B2:B14, ">80")2Counts cells where column A equals "Red" and column B values are greater than 80. Matches 2 values: 150 and 100.
Red752024-01-28COUNTIFS(C2:C14, ">=2023-01-01", C2:C14, "<=2023-12-31")4Counts cells in column C with dates in 2023. Matches 4 values.
Red852022-08-03AVERAGEIFS(B2:B14, A2:A14, "Green")93.33Calculates the average of column B values where column A equals "Green." (130 + 60 + 90 + 110 + 95 + 75)/6 = 93.33.
Green1102024-05-12AVERAGEIFS(B2:B14, A2:A14, "Red", B2:B14, ">90")125Calculates the average of column B values for "Red" where values are greater than 90. Average of 100 and 150.
Green952023-12-21AVERAGEIFS(B2:B14, C2:C14, ">=2023-01-01", C2:C14, "<=2023-12-31")115Calculates the average of column B values for rows with dates in 2023. Average of 130, 150, 120, 60, 90.
Blue1452023-07-04
Red1002024-03-17
Green752024-06-30
Red902022-12-11

Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 7. Розділ 4
We're sorry to hear that something went wrong. What happened?
some-alt