[Solved] need help with IF conditions

Discuss the spreadsheet application

[Solved] need help with IF conditions

Postby pluumy » Thu Jan 16, 2020 3:32 pm

Hey, i'm pretty new to OpenOffice Calc.

I made a list to keep track of the anime series i've watched so far and i want to add a cell that counts how many shows i haven't finished yet. But if i use the COUNTIF method to check whether the cells in the E-column are empty, then it also counts all the empty cells farther down the list where i haven't input any shows as of yet. So i wanted to to check if the C-cell is not empty and if so check, if the corresponding E-cell is empty. How do i code this?
Image
Last edited by robleyd on Thu Jan 16, 2020 11:28 pm, edited 2 times in total.
Reason: Add green tick
OpenOffice 4.1.5 / Windows 8.1
pluumy
 
Posts: 2
Joined: Thu Jan 16, 2020 3:18 pm

Re: need help with IF conditions

Postby FJCC » Thu Jan 16, 2020 4:08 pm

One possibility is a formula like
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(ISTEXT(C6:C20);ISBLANK(E6:E20))

ISTEXT and ISBLANK return TRUE or FALSE and TRUE = 1 and FALSE = 0. SUMPRODUCT multiplies together the results in each row and then adds all of those products. If both the ISTEXT and the ISBLANK are TRUE, the product is 1, otherwise the product is zero. So the result of the sum action is to count how many rows result in TRUE for both conditions.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7744
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] need help with IF conditions

Postby pluumy » Thu Jan 16, 2020 5:06 pm

Thank you very much. That worked perfectly!
OpenOffice 4.1.5 / Windows 8.1
pluumy
 
Posts: 2
Joined: Thu Jan 16, 2020 3:18 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests