RESOURCES

Some tools to make life easier. You’re welcome :)

• Coefficient - Live spreadsheet connections to 60+ business systems

• Lido - Automate your spreadsheets; accurately extract date from PDFs

• HubSpot - all-in-one CRM solutions for businesses of every size.

• Quickbooks - accounting software…still industry standard

• TransactionPro - quickly import, export or delete data in Quickbooks

• beehiiv - my choice for a newsletter operating system

• Carrd - free one-page website builder

• Notion - notetaking + project management + database

• Transistor - my favorite podcast host

• Fathom - AI-powered notetaking app

Excel Array Coercion

I was trying my hand at an Excel E-Sports project yesterday when I realized I needed to brush up on some lesser used functions.

The Excel competitions require some clever uses of string and text manipulation. I got stuck on some of the easy stuff because I’d forgotten how to do some basic things. It led me to search for the solutions below.

Overview

Excel hides a handful of powerful tricks that let you convert (or coerce) text and Boolean values into numbers. Once you know them, your formulas get shorter, faster, and more reliable. Here’s a concise guide worth bookmarking.

Core Coercion Techniques

1. Double Unary (--)
--"3" → 3
This was the biggest unlock for the project I was working on. After successfully splitting text out from one cell into an array, I needed to isolate the numbers and turn them back into actual numbers in the spreadsheet.

Two minus signs force text or Booleans into numbers. Example:

=SUMPRODUCT(--(A1:A5>10))

Counts how many values are greater than 10.

2. Unary Minus (-)
-TRUE → -1
A single minus coerces too, but it returns negatives unless doubled.

3. Multiply by 1 (*1)
(A1:A5>0)*1
Turns TRUE/FALSE into 1/0. Easier to read than -- for some people.

4. Add Zero (+0)
"45"+0 → 45
Cleans up numbers stored as text—common with imported data.

5. VALUE()
=VALUE("123") → 123
Explicit conversion. Clear, but wordier. I could have used this, but the double unary was cleaner in the formula bar.

6. N()
=N(TRUE) → 1
Great for Booleans and dates, but text becomes 0. Use sparingly. This wouldn’t work for several things I was doing - seemed mostly good for Booleans and Dates.

Extra Pro Tips

SUMPRODUCT as a universal SUM

Handles arrays without Ctrl+Shift+Enter.
Normally, array formulas require pressing Ctrl+Shift+Enter to work correctly. SUMPRODUCT avoids that—it automatically handles arrays without the special keystroke. (ctrl+Shift+Enter isn’t as used in modern Excel, but it was more common in older versions. The SumProduct trick works everywhere.)

=SUMPRODUCT(A1:A5, B1:B5)

Search patterns

=SUMPRODUCT(--ISNUMBER(SEARCH("P", A1:A10)))

Counts how many cells contain "P". This is part of what led me down this rabbit hole in the first place - I needed to sum the number of times different letters appeared throughout a giant string in a cell.

Extract numbers from codes
Same as above, part of the huge string that I split up had a letter and a number. I needed to match the letter with a key and then sum the numbers.
If format is always Letter+Number (W12):

=--RIGHT(A1,LEN(A1)-1)

Boolean logic shortcuts

• (cond1)*(cond2) → AND

• (cond1)+(cond2)>0 → OR

Example:

=SUMPRODUCT(((A1:A10>5)*(B1:B10<100))*C1:C10)

Quick Recap

• Use -- for clean, compact coercion.

• *1 and +0 are intuitive alternatives.

• VALUE() is explicit and beginner-friendly.

• N() is niche—best for Booleans/dates.

• SUMPRODUCT is your go-to array engine.

👉 Had you heard about Excel E-Sports before? Interested in me covering them in more detail? Walkthrough projects, live stream a project, etc?

Reply and let me know; I read every email.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Financial Operations Management
   Need to offload financial management but don’t want to hire full-time? I’ve been doing this for 20+ years. For $2M+ businesses.

2. Work with me
   I am available for consulting projects. Reach out and let’s chat.

3. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

4. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

5. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

6. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.