Sum visible only rows based on different column/cell value

[DukeofArmchair]

Hi Guys, I am somewhat of a novice with excel formulas and am struggling a bit to find a solution to what I am trying to accomplish. I am trying to sum only visible rows in the "Total USD (Monthly)" column with the qualifying criteria that column "Winner?" contains a 1 and "Overall Status" column is Empty. I know how to do the subtotal for visible rows only but cannot seem to figure it out with the if requirements.

I hope this makes sense, below is the formula that I am using now but it is not excluding hidden rows.

=SUMIFS(Table13[Total USD
(Monthly)],Table13[Winner?],"*"&"1"&"*",Table13[Overall
Status],"")

 

[Fluff]

Hi & welcome to MrExcel.
Do some of your column headers contain a line feed, or is that just the way you've copied the formula?

 

[DukeofArmchair]

Its just the formula copied directly from excel, I think its because its a "Table" giving titles rather than column letters and I have carriage returns in some of the header titles.

The below formula also accomplishes the same

=SUMIFS(AG4:AG296,AH4:AH296,"*"&"1"&"*",B4:B296,"")

 

[Fluff]

Do you have line feeds in the column headers?

 

[DukeofArmchair]

I don't think so, I am not even sure what that is

 

[DukeofArmchair]

Screenshot of what I am trying to accomplish

 

[Fluff]

Try

=SUM(FILTER(Table13[Total USD
(Monthly)],(ISNUMBER(FIND("1",Table13[Winner?])))*(Table13[Overall
Status]="")*MAP(Table13[Total USD
(Monthly)],LAMBDA(m,SUBTOTAL(3,m)))))

 

[DukeofArmchair]

This works PERFECTLY!! Thank you very much, I hope someday I can understand these complex formulas.

Thank you again, this makes my life much better

 

[Fluff]

You're welcome & thanks for the feedback.