Make SumIf ignore words?
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
3 Answers
3
0 MINS
excel
=SUMIF(E:E,"YES",C:C)
google-spreadsheet
=SUMIF(E3:E,"YES",C3:C)
Shareable link
I don't really want to remove the MINS, I'd rather use a solution that just ignores the " MINS" if possible
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:30
You aren't getting rid of them; you are replacing strings that are fouling up your calculations with a custom number format that will not foul up your calculations. It will look identical.
– Jeeped
Sep 9 '18 at 20:36
Just gives me 0 for some reason
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:46
See link added above.
– Jeeped
Sep 9 '18 at 20:51
Ahhh I just realized I'm supposed to remove the MINS I add 'cause it adds it for me, thanks
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:56
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”.
# “MINS”
It always says 0
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:42
It probably means that you didn’t set up the format right. Make sure you select the target cells (column C) before you apply the custom number format and that you remove the “ MINS” part from all the cells that you are trying to sum.
– Lorenzo Notaro
Sep 9 '18 at 20:49
Thanks, you and Jeeped helped me find my answer
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:57
=SUMPRODUCT(LEFT(C3:C5,LEN(C3:C5)-LEN(" mins"))*(D3:D5="yes"))
This is an array like calculation. As such full column references may bog your computer down with excess calculations.
Error Function LEFT parameter 2 value is negative. It should be positive or zero.
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:43
That is because you applied it to a cell that did was either empty or all the characters in the cell were less than the number of characters in " mins"
– Forward Ed
Sep 9 '18 at 21:34
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
@ForwardEd All the cells contain a number then " MINS", so every one would return text
– iLordOfAviation ROBLOX
Sep 9 '18 at 20:31