EXCEL / SUMIFS / ONE SMALL WORKFLOW

A small total for just the rows you need.

Sometimes you need the total for completed items, rather than every amount in a column. Let's use three rows so that the answer is easy to check yourself.

MYLAVL ·

Before we start

Use Excel that supports SUMIFS and a LAVL edition with the conditional-sum card. The product guide lists Excel 2007 or later for this function. The screenshots show PRO.

Full LAVL PRO editor overview
Actual PRO editor overview. It shows the wider tool, rather than a completed setup for this example.

Values for this example

SettingValue
CardConditional sum / SUMIFS
Sum rangeB2:B4
Condition rangeA2:A4
Condition"Complete"

A closer look at the card

Conditional sum · actual PRO reference card
Actual PRO conditional-sum card. The reference uses different sample ranges and text; replace them with B2:B4, A2:A4 and Complete for this exercise.

Let’s try it together

Put a tiny table in Excel

Enter Status and Amount in A1 and B1. Use Complete / 100, Pending / 200, and Complete / 300 in the next three rows, as shown below.

Set one condition in LAVL

Choose the conditional-sum card. Set the sum range to B2:B4, the condition range to A2:A4, and the text condition to "Complete". The ranges must cover the same rows.

Review the generated formula

Check that the result references your own columns. For this example, the formula is =SUMIFS(B2:B4,A2:A4,"Complete"). Copy the formula beginning with =, without the surrounding explanation.

Compare the answer before extending the range

Paste the formula into D2, outside the source table. The answer should be 400: 100 plus 300. Change Pending to Complete and the total should become 600. Once both checks work, extend the ranges to your real data.

What to look for

Type these three rows into A1:B4

Status (A)Amount (B)
Complete100
Pending200
Complete300

Formula for this practice table

=SUMIFS(B2:B4,A2:A4,"Complete")
Practice caseExpected result
Two Complete rows and one Pending row100 + 300 = 400
Change Pending to Complete100 + 200 + 300 = 600

Another view from the product guide

Reference image from the product guide
Example output from the product guide. It illustrates the tool's output; the 400 result in this exercise comes from the three rows shown here.

If it does not work yet

If the result is 0, check the status spelling, extra spaces, and whether the amounts are numbers. If Excel rejects the formula separators, use the separator required by your locale. Keep the sum and criteria ranges the same size.

Your next small step

A result you can check by hand makes a friendly first test. Then increase the range one step at a time.

Complete product guide →

Explore the FREE edition

FREE and PRO include different features. Check availability in your edition.

Explore the FREE edition Explore this feature

A practical example from the creator of MYLAVL. Product screenshots are reference images; results should be checked in your own application.

One small task at a time

Follow MYLAVL

Follow new examples with RSS → · Use this feed address in an RSS reader to follow newly published examples.