I have an excel sheet with following tables:

Project	Rate	Jan_Hrs	Feb_Hrs	Mar_Hrs
P1	3	3	4	6
P1	2	4	3	5
P2	3	3	2	5
P1	3	4	4	4
P3	4	3	3	2
P2	4	4	3	4
Total		66	60	79

I have an excel sheet with following tables:

Project	Rate	Jan_Hrs	Feb_Hrs	Mar_Hrs
P1	3	3	4	6
P1	2	4	3	5
P2	3	3	2	5
P1	3	4	4	4
P3	4	3	3	2
P2	4	4	3	4
Total		66	60	79

and I am getting the total with the formula =SUM($P$2:$P$7*Q2:Q7) Now I need to add a filter on Project, so that user can filter by Project and I want my formula to exclude hidden rows.

I have tried =Subtotal(109, $P$2:$P$7*Q2:Q7) but that give me error. I have tried some other combinations like =SUMPRODUCT(--SUBTOTAL(103,B2:B7),B2:B7,C2:C7) but nothing is working

• excel
• excel-formula

Share Improve this question Follow edited Mar 26 at 20:35 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 26 at 19:47 AhmadAhmad 1544 bronze badges 0

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You could try using the following formula:

• Formula used in cell Q10

=SUM(MAP($O2:$O7,LAMBDA(x,SUBTOTAL(103,x)))*(Q2:Q7*$P2:$P7))

---

And if you prefer to fill all at once :

=BYCOL(MAP(O2:O7,LAMBDA(y,SUBTOTAL(103,y)))*(Q2:S7*P2:P7),SUM)

---