CODEWITHSUNDEEP
excelgoogle-sheets
iLordOfAviation ROBLOX
iLordOfAviation ROBLOX

Reputation: 15

Make SumIf ignore words?

=SUMIF(E3:E,"YES",C3:C)

The above formula works in adding the numbers in C if the corresponding E cell is "YES", however my cells in C have "# MINS" in them, is there a way to make SumIf ignore words and only add the number?

SCREENSHOT OF SPREADSHEET: https://cdn.discordapp.com/attachments/358381825246101505/488443165364322327/Screenshot_1.png

Upvotes: 0

Views: 716

Answers (3)

user4039065
user4039065

Reputation:

  1. Get rid of the MINS. You can use Find & Replace or Text to Columns, etc.
  2. Create a custom number format of 0 \M\I\N\S.
  3. Use your original formula.

=SUMIF(E:E,"YES",C:C)


=SUMIF(E3:E,"YES",C3:C)

Shareable link

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

=SUMPRODUCT(LEFT(C3:C5,LEN(C3:C5)-LEN(" mins"))*(D3:D5="yes"))

enter image description here

This is an array like calculation. As such full column references may bog your computer down with excess calculations.

Upvotes: 1

Lorenzo Notaro
Lorenzo Notaro

Reputation: 89

If you’re using Google Spreadsheets, you have the possibility to format the numbers as you want. In the cells, store the numbers only so that SUMIF will work, then create a custom number format: in the toolbar - Format - Number - More Formats - Custom number format - type in # “MINS”.

Upvotes: 1

Related Questions