SUMIFS & COUNTIFS Conditional Analysis Test

What is the primary syntax difference regarding the position of sum_range between SUMIF and SUMIFS?

Which of the following statements correctly describe operational rules and differences between SUMIF and SUMIFS? (Select all that apply)

In target cell C6, calculate the total sales for the "North" region achieved specifically by rep "Alice" using SUMIFS.

When applying a logical comparison operator (such as greater than 100) as a hardcoded criterion in SUMIFS or COUNTIFS, how must it be written?

You want to sum amounts in column C where values in column B are greater than or equal to a threshold stored in cell D2. Which of the following formula expressions are valid in Excel? (Select all that apply)

In target cell C6, calculate total revenue in the "West" region for orders with revenue greater than or equal to $1,500 using SUMIFS.

What error does Excel return if your sum_range is C2:C20 (19 rows) but your criteria_range1 is A2:A15 (14 rows) in a SUMIFS formula?

Which of the following criteria expressions correctly utilize wildcard characters in COUNTIFS to match text strings? (Select all that apply)

In target cell C6, calculate the count of employees in the "Finance" department with tenure greater than 3 years using COUNTIFS.

What is the functional difference between the asterisk (*) and question mark (?) wildcards in Excel conditional criteria?

SUMIFS natively requires all criteria pairs to be true (AND logic). Which of the following methods are valid ways in Excel to sum sales for BOTH the "North" and "South" regions? (Select all that apply)

In target cell C6, calculate total units sold for all products starting with "Desk" using SUMIFS and an asterisk wildcard.

If column A contains the department "Marketing" and you write =COUNTIFS(A2:A10, "marketing"), how does Excel evaluate the criterion by default?

Which of the following built-in Excel functions natively accept multiple pairs of criteria ranges and criteria? (Select all that apply)

In target cell D6, calculate total invoice amount for orders placed between "2024-02-01" and "2024-02-28" (inclusive) using SUMIFS.

Which criterion string correctly instructs COUNTIFS to count only cells that are NOT blank in range A2:A50?

Scenario Background: The Logistics Director needs to audit stock levels for the ERP transition. Warehouse North and Warehouse East hold varied stock of component grades. The team needs to calculate the total units available for "Grade-A" components in "East Hub" where current stock is greater than 40 units.

Calculate the total available units for Grade-A components in East Hub with stock exceeding 40 units in target cell D7 using SUMIFS.

In target cell C6, calculate the average travel expense for the "Operations" department, excluding inactive entries with an expense of 0, using AVERAGEIFS.

To calculate the sum of sales in C2:C50 where sales values are between 1,000 and 5,000 inclusive, how must the criteria ranges be structured in SUMIFS?

Under which of the following scenarios will a syntactically valid =SUMIFS(C2:C20, A2:A20, "North", B2:B20, ">100") return 0 without raising an error? (Select all that apply)

In target cell C7, count the number of invoices with an invoice code ending in "-US" that have a status of "Paid" using COUNTIFS.

What logical relationship is applied across all criteria pairs in a SUMIFS or COUNTIFS formula?

Scenario Background: Sales Management evaluates Q1 performance bonus eligibility. Representatives earn bonus points on "Enterprise" segment closed accounts signed in "Q1" where the deal volume is at least $5,000. Calculate the total qualified deal value to determine the bonus pool.

In target cell D7, calculate the total deal volume for Enterprise clients closed in Q1 with individual deal values of at least $5,000 using SUMIFS.

In target cell D6, calculate total sales for the target region specified in F2 ("North") where the Quota % in column C is greater than or equal to the minimum threshold in G2 (100) using SUMIFS.

If a product catalog contains literal asterisks in item names (such as "Model*Pro"), how do you search for that exact literal asterisk in a COUNTIFS criterion?

Which of the following comparison operators are supported inside criteria strings for SUMIFS, COUNTIFS, and AVERAGEIFS? (Select all that apply)

Scenario Background: Payroll Accounting needs to reconcile overtime wages for the manufacturing facility. Operations requires the total overtime pay for the "Logistics" department on the "Night" shift where overtime hours exceed 10 hours.

Calculate the total overtime pay for Logistics workers on Night shifts who logged more than 10 overtime hours in target cell E7 using SUMIFS.

What is a major performance consideration when using entire column references like =SUMIFS(C:C, A:A, "North", B:B, ">100") instead of explicit ranges like C2:C1000?

Scenario Background: The Quality Assurance department tracks inbound component batches. The manager needs to count the number of defective batches delivered by vendor "Apex Industrial" where the detected defect count exceeds 2 and the inspection status is "Flagged".

In target cell C7, count the number of batches from "Apex Industrial" with defect counts greater than 2 and status "Flagged" using COUNTIFS.

Scenario Background: A medical center administration analyzes consultation efficiency. The clinic director needs to determine the average appointment duration in minutes for consultations conducted by "Dr. Vance" in the "Cardiology" department, excluding cancellations and no-shows where duration was recorded as 0 minutes.

In target cell D7, calculate the average appointment duration for Dr. Vance's Cardiology consultations, excluding no-shows (duration <> 0), using AVERAGEIFS.