SOLVED

# ignoring black cell (or part of the formule)

Copper Contributor

# ignoring black cell (or part of the formule)

hello,

table 1

table 2

out of the first table i collect data, to my dashboard (table 2)

the problem i have is: that under kind of trade is a dropdownlist used as a filter with: real trade, example, mist trade and so on.

now is my question is: how do i make a formule were i get all the results if B3 is empty and the results if there's text in B3 like "real trade"

=SUMIFS('TRADES ENTRY'!\$E:\$E;'TRADES ENTRY'!\$K:\$K;DASHBOARD!B6;'TRADES ENTRY'!\$F:\$F;DASHBOARD!\$C\$3;'TRADES ENTRY'!\$G:\$G;DASHBOARD!\$B\$3)

thanks for helping!

8 Replies

# Re: ignoring black cell (or part of the formule)

=SUMIFS('TRADES ENTRY'!\$E:\$E,'TRADES ENTRY'!\$K:\$K,DASHBOARD!B6,'TRADES ENTRY'!\$F:\$F,DASHBOARD!\$C\$3,'TRADES ENTRY'!\$G:\$G,IF(DASHBOARD!\$B\$3="","*",DASHBOARD!\$B\$3))

This formula returns the expected result in my sample worksheet.

# Re: ignoring black cell (or part of the formule)

Thanks for your reaction! unfortunately it doesn't work my excel is in dutch (netherlands) maybe this gives a issue.
i get a arror

any other ideas ?

kind regards Mark

# Re: ignoring black cell (or part of the formule)

=SOMMEN.ALS('TRADES ENTRY'!\$E:\$E;'TRADES ENTRY'!\$K:\$K;DASHBOARD!B6;'TRADES ENTRY'!\$F:\$F;DASHBOARD!\$C\$3;'TRADES ENTRY'!\$G:\$G;ALS(DASHBOARD!\$B\$3="";"*";DASHBOARD!\$B\$3))

Which error message did you get when you opened the attached file from my first reply? I've translated the formula into dutch. Below you can see the results from my sheet. The formula works in Excel 2013 and Excel for the web on my computer.

Sheet "DASHBOARD":

Sheet "TRADES ENTRY":

# Re: ignoring black cell (or part of the formule)

never mind! i copied only the last bid of your formule, i didn't see the part IF(DASHBOARD......
i copied only the part ="",",*",DASHBOARD!\$B\$3))
thank you so much!! you're a life saver!!
i was bussy for a full week with this, and looked so many video's but could not find it..

thanks again! have a great day!

# Re: ignoring black cell (or part of the formule)

You are welcome! Have a great day as well.

# Re: ignoring black cell (or part of the formule)

sorry for bothering you again. but one last question.
i try to make it also for the second dropdownlist (long/short or empty cell, but i can't use IF twice right ?
do i use AND than and how will the formule look?

thank you so much!
best response confirmed by HansVogelaar (MVP)
Solution

# Re: ignoring black cell (or part of the formule)

=SOMMEN.ALS('TRADES ENTRY'!\$E:\$E;'TRADES ENTRY'!\$K:\$K;DASHBOARD!B6;'TRADES ENTRY'!\$F:\$F;ALS(DASHBOARD!\$C\$3="";"*";DASHBOARD!\$C\$3);'TRADES ENTRY'!\$G:\$G;ALS(DASHBOARD!\$B\$3="";"*";DASHBOARD!\$B\$3))

You are welcome! I've added dropdowns in cells B3 and C3 in the attached file. This formula checks in addition if in cell C3 long/short or an empty cell is selected.

# Re: ignoring black cell (or part of the formule)

Thank you so much again!!
don't know what i'm doing wrong, i tried the same thing and it didn't work.
but yours works! you made my day!
thank you!
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: ignoring black cell (or part of the formule)

=SOMMEN.ALS('TRADES ENTRY'!\$E:\$E;'TRADES ENTRY'!\$K:\$K;DASHBOARD!B6;'TRADES ENTRY'!\$F:\$F;ALS(DASHBOARD!\$C\$3="";"*";DASHBOARD!\$C\$3);'TRADES ENTRY'!\$G:\$G;ALS(DASHBOARD!\$B\$3="";"*";DASHBOARD!\$B\$3))

You are welcome! I've added dropdowns in cells B3 and C3 in the attached file. This formula checks in addition if in cell C3 long/short or an empty cell is selected.