Summing raw OR condition sums
A row satisfying both conditions produces two instead of one.
Audit row-wise Boolean masks and counts
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return the six source IDs in order, followed by four numeric 0/1 columns: Team equals F2; Ready from column C; Team match AND Ready; Ready OR Units at least F4. Team equality is case-insensitive and punctuation is literal. Add a seventh row with Total in the first column and the sum of each mask in the other four columns. Each physical source record counts once even if its ID repeats. Ready cells must be actual TRUE/FALSE values; units and threshold are finite numbers within ±1,000,000. Keep all six records, including numeric zero and signed units.
Change an actual source value
Choose a cell and its stored type, apply the value, then check the complete report again. A text key can contain literal punctuation. Ready values are actual Booleans. Invalid source types leave the worksheet unchanged.
This draft matches the current cell.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:K11. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Convert real Booleans to 0/1, combine row conditions with AND and OR, and reconcile counts without double-counting overlaps.
THE QUICK ANSWER01
Convert real Booleans to 0/1, combine row conditions with AND and OR, and reconcile counts without double-counting overlaps.
ƒx
=LET(team,--(B2:B7=F2),ready,--C2:C7,both,team*ready,either,--((ready+(D2:D7>=F4))>0),VSTACK(HSTACK(A2:A7,team,ready,both,either),HSTACK("Total",SUM(team),SUM(ready),SUM(both),SUM(either))))| ID | Team match | Ready as 0/1 | Team AND ready | Ready OR enough units |
|---|---|---|---|---|
| Q001 | 1 | 1 | 1 | 1 |
| Q002 | 0 | 1 | 0 | 1 |
| Q003 | 1 | 0 | 0 | 1 |
| Q004 | 0 | 0 | 0 | 0 |
| Q005 | 1 | 1 | 1 | 1 |
| Q006 | 1 | 0 | 0 | 1 |
| Total | 4 | 3 | 2 | 5 |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Work through the first source
The Team mask is 1,0,1,0,1,1. Ready is 1,1,0,0,1,0. Their AND mask is 1,0,0,0,1,0. The OR mask is 1,1,1,0,1,1. The four totals are 4,3,2,5.
Keep OR as a row decision
A row that is Ready and meets the unit threshold belongs to the union once. Summing the unnormalized condition sums would count that overlap twice.
Use row arrays before reducing
AND and OR functions reduce their arguments to an overall answer. This report needs six separate decisions before SUM reduces the numeric masks into four totals. Source punctuation is compared literally, and repeated IDs remain separate records.
Read the full step-by-step walkthrough
- Compare each source row
B2:B7=F2 returns one Boolean per record. The double unary converts each TRUE/FALSE to numeric 1/0.
- Keep Boolean source types
The Ready column contains actual Booleans. --C2:C7 converts the six values individually. Text TRUE and numeric one are outside this exercise's source contract.
- Intersect conditions row by row
Multiplying the two 0/1 arrays returns one only where both conditions hold on that same row. AND over entire ranges would collapse the inputs into one result.
- Normalize an OR overlap
Adding Ready to the threshold comparison can give two. Comparing the sum with zero and converting to 0/1 counts the row once even when both conditions hold.
- Keep IDs beside the four masks
HSTACK keeps every original ID aligned with its own four decisions. Nothing is filtered or deduplicated.
- Reconcile complete counts
SUM now receives explicit numeric masks. Stack one Total row below the six original records and inspect all four totals.
Things to watch
Using AND or OR on whole columns
A single aggregate Boolean cannot supply six independent row decisions.
Treating TRUE text as a Boolean source
Use actual Boolean cells, then explicitly coerce their values.
About this example
Original bounded arrays, complete typed outputs and current/changed-source browser calculations; native recalculation and edit/save remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.