SOLVED

# Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

Brass Contributor

# Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

Good morning!

I am trying to count the number of vacancies in site GHI if the square footage (SF) is created by the use of a formula. But I want to do it as it is laid out on the attached.

I am looking for the fight formula to enter into the yellow highlighted cell on the attached (cell C12).

I believe it is a combination of COUNTIFS(SITES!B:B,B2,SITES!N:N,B12) and SUMPRODUCT(--ISFORMULA(SITES!P:P)) but I don’t know how to merge them in order to understand the answer to this question:

Depending on the text in USE:B2, filter SITES: B:B by that text, then count cells in SITES: N:N which include the text that exists in USE:B12, if SITES: P:P is a formula.

6 Replies

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

On which Excel platform/version you are?

best response confirmed by Danger_SF (Brass Contributor)
Solution

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

``=LET(sites,SITES!B3:B13,type,SITES!N3:N13,SF,SITES!P3:P13,filtered,FILTER(sites,(sites=\$B\$2)*(type=\$B\$12)*(ISFORMULA(SF))),ROWS(filtered))``

Office 365

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

You guys are amazing. I am in awe. It actually indices uncontrollable laughter to see this artistry at work. SMH. I'm at a loss. Thank you for your help.

OH, and I'll be back for more help soon! :D

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

Glad I could help! Thank you for providing the sample workbook. That goes a long way in this forum in getting a quick solution (or three).

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

An alternative for older versions of Excel could be SUMPRODUCT.

``=SUMPRODUCT((\$B\$2=SITES!\$B\$3:\$B\$13)*(USE!\$B\$12=SITES!\$N\$3:\$N\$13)*ISFORMULA(SITES!\$P\$3:\$P\$13))``
1 best response

Accepted Solutions
best response confirmed by Danger_SF (Brass Contributor)
Solution

# Re: Ugh. I'm at my wits end. Trying to combine two formulas and I'm stuck.

``=LET(sites,SITES!B3:B13,type,SITES!N3:N13,SF,SITES!P3:P13,filtered,FILTER(sites,(sites=\$B\$2)*(type=\$B\$12)*(ISFORMULA(SF))),ROWS(filtered))``