# Gemma: ask for an Excel formula and check the source rows

Give Gemma cell addresses, conditions and Excel settings, then check a SUMIFS draft against month boundaries, payment status and a zero amount.

Canonical URL: https://boswidget.ru/en/gemma-excel-formula-check-source-rows/

![A woman points a pencil at one of the highlighted rows on a vertical grid.](https://boswidget.ru/assets/images/articles/gemma-excel-formula-check-1200.ff48edae263b.webp)

Decide which rows should contribute to the total before checking the formula.

You need the total for paid orders in one region during a month. You describe the task to Gemma, paste the formula into Excel and get a number. That number alone says little about whether the right rows were selected: it could include the next month or an order with a different status. The prompt therefore needs cell addresses, an exact rule and a small sample you can add up by hand.

We will work through the request using seven rows. This example uses English function names and commas between arguments. For your own workbook, tell Gemma the actual function language and argument separator your Excel accepts.

## Write down the selection rule first

A row contributes to the total only when all conditions hold at once: the date falls on or after the start of the month and before the next month, the region matches your selection, and the order is marked as paid. All conditions must apply together. A total for either of two regions would be a different rule and needs a separate description.

Put the criteria in separate cells: G2 holds the start of the period, G3 its exclusive upper boundary, G4 the required region and G5 the status. You can then change the month or region by changing those cells. Tell Gemma to reference them rather than type dates and names directly into the formula.

The example amounts all use the same unit. Zero means a known amount of zero. If a blank cell in your real data means the amount has not been entered yet, deal with those rows separately first. Do not let the model quietly fill gaps with zeros just to produce a total.

## Include rows that should be excluded

Copy a small sample without customer names or other unnecessary data. Keep the exact column addresses. Alongside valid rows, include one before the period, the first day of the next month, a different region and an unpaid order. Otherwise, a formula missing a condition could still return the expected total.

In the example below, DATE creates an Excel date and TIME adds a time as a fraction of a day. Together in A8, they represent 31 March at 18:30. This checks that the whole final day of the month is included. North, South, Paid and Waiting are sample labels. Replace them with your actual values in your workbook, keeping the data consistent with G4:G5.

## The complete prompt for Gemma

```
I need an Excel formula with English function names and commas between arguments. The data below is a practice sample. A contains Excel date and time values, and D contains numbers in the same unit. There are no blank cells or errors in the sample.

A1: Date; B1: Region; C1: Status; D1: Amount.
Rows A:D:
2: =DATE(2026,2,28) | North | Paid | 1200
3: =DATE(2026,3,1) | North | Paid | 1000
4: =DATE(2026,3,15) | North | Paid | 0
5: =DATE(2026,3,20) | South | Paid | 700
6: =DATE(2026,3,25) | North | Waiting | 900
7: =DATE(2026,4,1) | North | Paid | 800
8: =DATE(2026,3,31)+TIME(18,30,0) | North | Paid | 400

Criteria cells:
G2: =DATE(2026,3,1)
G3: =DATE(2026,4,1)
G4: North
G5: Paid

Sum D2:D8 only when A >= G2, A < G3, B = G4 and C = G5 all hold at once. Reference G2:G5. Do not change the source data or add conditions. The zero in D4 remains an actual zero.

Return a formula for J2, an explanation of each range/criterion pair, the numbers of included and excluded rows with a reason for each, then a manual addition of the selected amounts. If a condition is ambiguous, ask a question first. No macros or code to modify the workbook are needed. Do not claim the formula has been run in Excel.
```

The DATE and TIME expressions shown in the rows belong in column A. When you set up the sample in Excel, put each value in its own cell: the date in A, region in B, status in C and amount in D. The prompt gives Gemma a description of this data. You then check the result in the workbook itself.

If the complete prompt does not fit the available input field, reduce the sample while keeping rows that test the conditions. Shortening it must not remove the meaning of the period or status. Check file attachment support and the Gemma version in your own interface; pasted text is enough for this task.

## The result required by this rule

The formula below is a worked example for this cell layout. You can inspect its conditions and check it in your own workbook.

```
=SUMIFS($D$2:$D$8,$A$2:$A$8,">="&$G$2,$A$2:$A$8,"<"&$G$3,$B$2:$B$8,$G$4,$C$2:$C$8,$G$5)
```

SUMIFS starts with the sum range, D2:D8. It is followed by pairs: a range to check and a criterion. Here A2:A8 is used twice for the period boundaries, followed by checks on B2:B8 and C2:C8. Every range covers the same rows. Dollar signs keep references fixed when the formula is copied. If you need a formula for several different groups, specify how it should be copied separately.

- Row 3 is included: it is at the start of the period and has the required region and status. It contributes 1000.
- Row 4 is included too. Its amount is 0, so the final number alone cannot show whether it was selected. Check the row list as well.
- Row 8 is included: 31 March at 18:30 is still before the start of 1 April. It contributes 400.
- Row 2 is before the period. Row 7 is already in the next month. Rows 5 and 6 are excluded because of their region and status, respectively.

The manual reference total for this sample is 1000 + 0 + 400 = 1400. It follows from the stated rule. If Gemma also returns 1400, still check its row list and formula: one matching total does not prove that every condition is correct.

The boundary A &lt; G3 includes the row with an evening time on 31 March and excludes the start of 1 April. A &lt;= DATE(2026,3,31) would cut the period off at midnight at the start of the last day. That is why the example uses the start of the next month as an exclusive boundary rather than asking the model to invent a suitable end time.

## Check formula entry and row selection separately

First make sure Excel accepts the formula. If an error appears, compare the function names, argument separator, parentheses and references. Microsoft states that the separator depends on system and Excel regional settings. Do not assume the English SUMIFS name and commas in this example will work unchanged in every installation.

Then check the selection: start and end dates, region, status and amounts. In your own workbook, make sure dates and amounts are stored as the correct value types. Text that looks like a date needs separate attention. Establish its format first, then decide how to prepare the column for calculation.

Before using the formula on real data, check the full range: the sample ends at row 8, but your table may be longer and keep growing. A short reference will not automatically cover new records. Give Gemma the actual workbook structure and decide separately how the range will expand.

Google describes accuracy and task complexity limitations in the Gemma 4 model card. Check the version and settings in your own interface separately. With formulas, tie your checks to things you can inspect: addresses, criteria, selected rows and the result in Excel.

## Fix the specific mismatch

If the total differs, keep the formula you entered and Excel's exact result or error message. Include the application settings, source sample, rule and manual calculation in a new request. Gemma will then have the full context even in a separate chat.

```
Help me find the cause of a mismatch in an Excel formula. Application, function language and argument separator: {excel_settings}.

Column addresses, a small data sample and criteria cells:
{sample_and_cells}

Row selection rule:
{rule}

The formula I entered:
{formula}

Expected rows and manual total:
{expected}

Actual value or exact Excel error message:
{actual}

Compare the ranges, comparison operators and references with the rule. Show the specific mismatch and suggest the smallest change needed. Keep the other conditions. Do not hide the error with IFERROR or replace it with zero. Give me rows I can use to check the correction in Excel. If information is missing, say what is needed.
```

After the correction, repeat the check in a copy of the workbook. Keep the formula with its criteria and check sample. When columns or accounting rules change, this set will help you see what needs revisiting.

[Prompts for Gemma](https://boswidget.ru/en/prompts-for-gemma/)

- [Google: the Gemma model family](https://ai.google.dev/gemma/docs)
- [Google: Gemma 4 capabilities and limitations](https://ai.google.dev/gemma/docs/core/model_card_4)
- [Microsoft: SUMIFS arguments and ranges](https://support.microsoft.com/en-us/excel/functions/sumifs-function)
- [Microsoft: formula entry errors and separators](https://support.microsoft.com/en-us/excel/how-to-avoid-broken-formulas-in-excel)
- [Microsoft: the DATE function](https://support.microsoft.com/en-us/excel/functions/date-function)
- [Microsoft: the TIME function](https://support.microsoft.com/en-us/excel/functions/time-function)