[Solved] SUM cells with certain conditions

Discuss the spreadsheet application
Post Reply
Mikeyy
Posts: 31
Joined: Mon Jan 31, 2011 8:08 am

[Solved] SUM cells with certain conditions

Post by Mikeyy »

Hello all,

I'm in need for a formula. :D
I know how to solve this with SUMIF, if data wasn't text, but since data is text, I'm really not sure how to solve it.

Attached you will find test file.
I need to SUM all data from row 2, if number in row 1 = 2 and data in row 2 starts with "b".
Wanted result for test file is 13.

Anyone know how I can do this without making whole new table which will first filter out "b" text fileds and their attached numbers?

Thank you.
Attachments
TEST.ods
(9.77 KiB) Downloaded 87 times
Last edited by Hagar Delest on Thu Feb 22, 2018 4:46 pm, edited 1 time in total.
Reason: tagged solved
Top
FJCC
Moderator
Posts: 9278
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUM cells with certain conditions

Post by FJCC »

Try

Code: Select all

=SUMPRODUCT(B1:T1 = 2;LEFT(B2:T2) = "b";VALUE(SUBSTITUTE(B2:T2;"b";"")))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Top
Mikeyy
Posts: 31
Joined: Mon Jan 31, 2011 8:08 am

Re: SUM cells with certain conditions

Post by Mikeyy »

Thank you, that works! I just don't understand how. :D
Need to dig deeper into SUMPRODUCT to understand it.
Top
Post Reply

Return to “Calc”