COUNTIFS and SUMIFS Formula Generator for Google Sheets

Choose what to count, add up or average, add up to three conditions, and the generator writes the COUNTIFS, SUMIFS or AVERAGEIFS formula for Google Sheets. It takes care of the parts that usually go wrong: the operator inside the quotes with the cell joined by &, dates written with DATE(), the last day of a date range when the dates have times, and a real * or ? in the text. Nothing you type leaves your browser.

1. What to calculate

2. Only the rows where…

And (optional)

And also (optional)

Your formula

    Examples the generator builds

    We pasted each of these into Google Sheets on a table of 12 orders (date in A, rep in B, status in C, item in D, amount in E, notes in G, rows 2 to 13) and got the result shown. One date in that table has a time (January 31 at 18:30), and the item column contains “Apple*” and “Kiwi?” with real * and ? characters.

    You want Formula Result
    Two text conditions =COUNTIFS(C2:C13, "Paid", B2:B13, "Ana") 3
    Greater than a number =COUNTIFS(E2:E13, ">100") 5
    Greater than the value in L1 =COUNTIFS(E2:E13, ">"&L1) 5
    Not equal to =COUNTIFS(C2:C13, "<>Paid") 5
    Contains a word =COUNTIFS(D2:D13, "*apple*") 5
    Contains the text in L2 =COUNTIFS(D2:D13, "*"&L2&"*") 5
    Starts with / ends with =COUNTIFS(D2:D13, "apple*") / "*apple" 4 / 1
    A real * in the text =COUNTIFS(D2:D13, "Apple~*") 1
    Not blank / blank =COUNTIFS(G2:G13, "<>") / "" 9 / 5
    Between two numbers =COUNTIFS(E2:E13, ">=50", E2:E13, "<=100") 5
    Between two dates =COUNTIFS(A2:A13, ">="&DATE(2026,1,1), A2:A13, "<"&DATE(2026,1,31)+1) 4
    Between the dates in L3 and L4 =COUNTIFS(A2:A13, ">="&L3, A2:A13, "<"&L4+1) 4
    On one date =COUNTIFS(A2:A13, ">="&DATE(2026,1,31), A2:A13, "<"&DATE(2026,1,31)+1) 1
    Sum between two dates =SUMIFS(E2:E13, A2:A13, ">="&DATE(2026,1,1), A2:A13, "<"&DATE(2026,1,31)+1) 445
    Sum with two conditions =SUMIFS(E2:E13, C2:C13, "Paid", B2:B13, "Ana") 420
    Sum where the item contains “apple” =SUMIFS(E2:E13, D2:D13, "*apple*") 470
    Average with a condition =AVERAGEIFS(E2:E13, C2:C13, "Paid") 136.67

    The formulas with a semicolon separator, for spreadsheets set to a European or Latin American locale, follow the same rules as our separator converter; we tested the comma versions shown above.

    Mistakes the generator avoids

    We also ran the versions that go wrong. None of them showed an error; they just returned a different number.

    • The cell inside the quotes. ">L1" looks for the text “L1” and returned 0. The generator writes ">"&L1.
    • “On or before” the last day. "<="&DATE(2026,1,31) left out the order on January 31 at 18:30. The generator writes “before the next day”, "<"&DATE(2026,1,31)+1, and tells you why.
    • A * or ? meant literally. Without the ~ in front, the * is a wildcard: "apple*" matched 4 items, while "Apple~*" matched only the one with a real *. The generator adds the ~ for you.
    • Wildcards on numbers. “contains” only finds text, so the generator refuses it for numbers and dates instead of building a formula that returns 0.
    • The argument order of SUMIFS. The range to add comes first. With SUMIF’s order, the result was 0.

    Two things the generator can’t do from a form: OR conditions and numbers stored as text. For “this or that”, see SUMIFS with multiple criteria; a list in braces, such as {"Food","Travel"}, summed only the first value in our tests. Numbers stored as text are skipped without an error; our guide to converting text to numbers shows how to fix them.

    Related guides