Subtotal on Filtered Rows using Sumproduct - Wildcard substitute

[Sonymacd]

I would love some help correcting this pickle! I need to show the total of 1 column, based on whether another column contains certain text.

Looking through posts and blogs, I have gotten this far, but it will only total if BBA is the only Program in column P. It would solve my problem if I could use wildcard around BBA but of course sumproduct doesn't work that way.

=SUMPRODUCT((P6:P215="BBA")*(SUBTOTAL(109,OFFSET(I6,ROW(I6:I215)-MIN(ROW(I6:I215)),0))))

I have the Awards in Column I and Eligible Programs in Column P (which lists one or many programs). When I filter on Column F to show only Entrance Awards, I would like the BBA Award Total to be reflective of only the rows in the view.


Grateful for anyone able to assist!

 

[Fluff]

How about

=SUMPRODUCT((ISNUMBER(SEARCH("BBA",P6:P215)))*(SUBTOTAL(109,OFFSET(I6,ROW(I6:I215)-MIN(ROW(I6:I215)),0))))

 

[Sonymacd]

How about

=SUMPRODUCT((ISNUMBER(SEARCH("BBA",P6:P215)))*(SUBTOTAL(109,OFFSET(I6,ROW(I6:I215)-MIN(ROW(I6:I215)),0))))

You, my friend, are a genius! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ootkhopdi]

Hi Everyone,,
Please tell me how sum of every nth row in filtered data,
i mean if i filter my data in excel from row 4 to row 400, i want to sum every 3rd row after data, in row 401, (Sum of Row 4,7,10,....), in row 402,(Sum of Row 5,8,11.......) in row 403( Sum of row 5,9,12,......) Please tell me how can do it..


Thanks in advance

 

[Fluff]

As this is a totally different question from the op, you need to start a new thread. Thanks

 

[ootkhopdi]

T

As this is a totally different question from the op, you need to start a new thread. Thanks

Thanks for suggestion