DEV Community: billy6go

INDEX/MATCH with SUMIF in Excel: two patterns that solve different problems

billy6go — Sat, 01 Aug 2026 17:36:22 +0000

INDEX/MATCH with SUMIF sounds like one formula pattern, but people usually mean one of two different tasks:

sum rows that match a condition while choosing the sum column from a header; or
return one value from the row where two conditions match.

Those tasks need different formulas. Separating them first prevents a formula that looks plausible but answers the wrong question.

Pattern 1: choose the SUMIF column from a header

Suppose:

column A contains a region;
columns B:D contain monthly amounts;
cell E2 contains the region to sum;
cell F1 contains the month header to select.

Use:

=SUMIF($A$2:$A$100,$E2,INDEX($B$2:$D$100,0,MATCH(F$1,$B$1:$D$1,0)))

Read it from the inside out:

MATCH(F$1,$B$1:$D$1,0) finds the exact month column.
INDEX($B$2:$D$100,0,...) returns that complete column as the sum range.
SUMIF($A$2:$A$100,$E2,...) adds values only for rows whose region matches E2.

The final 0 in MATCH matters. It requests an exact header match. Without it, an unsorted header row can produce a believable but incorrect result.

If you want a compact comparison of SUMIF, SUMIFS, XLOOKUP, VLOOKUP, and INDEX/MATCH, use this formula-pattern guide. It keeps the examples, assumptions, and common argument-order trap together.

Assumptions to make visible

$A$2:$A$100 and the selected sum column cover the same rows.
The month header in F1 appears once in B1:D1. MATCH returns the first duplicate.
Amount cells are numeric rather than numbers stored as text.
Full-column array ranges are avoided so recalculation stays bounded.

If the sum has two row conditions — for example region and product — use SUMIFS instead of forcing both conditions through INDEX/MATCH:

=SUMIFS($D$2:$D$100,$A$2:$A$100,$F2,$B$2:$B$100,$G2)

SUMIF puts the optional sum range last. SUMIFS puts the sum range first. Mixing those argument orders is a common source of incorrect formulas.

Pattern 2: return one row with two criteria

Now suppose:

column A contains account IDs;
column B contains months;
column D contains the value to return;
F2 and G2 contain the requested account and month.

This is a lookup, not an aggregation. In current Excel, XLOOKUP can express it directly:

=XLOOKUP(1,($A$2:$A$100=$F2)*($B$2:$B$100=$G2),$D$2:$D$100,"Not found")

For an older-workbook INDEX/MATCH pattern:

=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=$F2)*($B$2:$B$100=$G2),0))

Each comparison produces TRUE or FALSE. Multiplication turns the row where both tests are true into 1, and the lookup searches for that exact value.

Older perpetual Excel releases may require confirming the INDEX/MATCH version as an array formula. Current Microsoft 365 evaluates it directly.

Check duplicates before trusting the result

Both lookup formulas return the first matching row. They do not warn that the same account-and-month pair appears twice.

If duplicates are valid, decide whether the task should:

return all matches with FILTER;
add them with SUMIFS; or
reject the duplicate key in the source data.

That decision belongs in the task definition, not in a hidden formula assumption.

A short decision rule

One condition and a sum: SUMIF.
Several conditions and a sum: SUMIFS.
One matching record in current Excel: XLOOKUP.
One matching record with older-version compatibility: INDEX/MATCH.
A header chooses which column to sum: SUMIF with an INDEX/MATCH sum range.

Before filling any formula down, test one known match, one known miss, and one duplicate case. A formula is only useful when its assumptions are as visible as its result.

Anonymous survey design starts with a data inventory, not a checkbox

billy6go — Thu, 30 Jul 2026 05:23:02 +0000

“Anonymous” is often implemented as a missing name field.

That is a start, but it is not a design method. A survey can omit a name and still connect an answer to an account, invitation token, IP address, cookie, device identifier, tiny team, or revealing free-text detail.

The useful engineering question is:

Which identity paths does this task actually need, and what happens if we remove each one?

Want to inspect the pattern before the implementation checklist? CandorKit creates one account-free survey with separate respondent and owner links and a seven-day database expiry. It is for low-risk feedback, not protected reporting or regulated research.

1. Inventory the data path before writing the form

List every value that can enter, leave, or persist in the system:

survey title and questions
response choices and free text
timestamps
account or invitation identifiers
network and device metadata
analytics events
exported files and copied summaries
administrative secrets

Then label each item as required, optional, or prohibited for the specific task. “The framework collects it by default” is not a requirement.

CandorKit’s application database stores survey text, answers, timestamps, a hash of the private owner key, and an expiry timestamp. It does not attach names, account IDs, IP addresses, user agents, or cookie IDs to answers.

That statement is narrower than “nobody can identify a respondent.” Network providers still process ordinary requests. Question wording, small groups, unique events, and free text can reveal a person even when the application omits identity columns.

2. Split respondent access from owner access

A public response URL and a private results URL serve different trust roles. Reusing one bearer link for both makes accidental disclosure easier.

CandorKit creates a private owner secret and places it in the URL fragment. Browsers do not send fragments in the normal HTTP request path. The server stores a hash rather than the raw key.

This design has a cost: there is no recovery workflow. If the owner loses the private link, the service cannot reconstruct access. That trade-off should be visible before the survey is shared, not hidden in support documentation.

3. Give retention an executable boundary

“We delete data when it is no longer needed” is not an operational rule.

An executable rule needs:

a known expiry timestamp;
rejection of new reads or writes after expiry;
a cleanup process;
visible user wording;
a statement about copies outside the system.

CandorKit assigns each survey a seven-day database expiry, rejects expired access, and runs scheduled cleanup. CSV exports, screenshots, copied analysis, and other downstream files are outside that automatic deletion boundary.

4. Keep analytics away from sensitive payloads

An analytics event should describe product behavior, not survey content. Do not send titles, questions, answers, owner keys, or private links into session replay or event properties.

The same rule belongs in logging and error reporting. A masked UI is not enough if a request body or exception payload copies sensitive text elsewhere.

5. Design the questions as part of the privacy model

Infrastructure cannot rescue a survey that asks respondents to identify themselves in prose.

For sensitive feedback:

avoid asking for names, exact locations, employee IDs, or unnecessary demographics;
warn people not to name themselves or others in free text;
avoid combinations that isolate one person in a small group;
choose multiple-choice or ranges when exact values add no task value;
do not use a lightweight anonymous survey for emergencies, regulated reporting, or protected whistleblowing.

The practical lesson is that anonymous survey design is subtractive. Remove identity paths, shorten retention, separate privileges, and name the remaining limits.