Summing with two-way criteria while using array formulas (or dynamic range)

meamuka

New Member
Joined
Aug 27, 2016
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi Guys!

Basically I have a table like this:

///
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1Date
1-Jan-22​
2-Jan-22​
3-Jan-22​
4-Jan-22​
2TypeDurationDurationDurationDurationDurationDurationDurationDuration
3Day of week
7​
1​
2​
3​
7​
1​
2​
3​
4Name
5Joshua
8​
0​
8​
8​
????????????
6Jannet
8​
8​
0​
8​
7James
8​
8​
8​
0​
8Jack
0​
8​
8​
8​
9

The Names are filtered from another table and are dynamic.

I am trying to sum the values based on two criteria ("Duration" and "Day of week"), like this:

=SUMIFS($B5:$E5,$B$2:$E$2,G$2,$B$3:$E$3,G$3)

This gives a correct result, but I want the formula to dynamically update, based on the dynamic formula that contains the names. So I tried this:

=IF(A5#<>0,SUMIFS($B5:$E5,$B$2:$E$2,G$2,$B$3:$E$3,G$3),0)

This goes way down the table, but it always returns the sum of the first row.

Is anyone aware of a solution to this? It doesn't have to be this way but I want it to update the rows automatically and of course, return a correct solution.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I'm not sure why you're having issues, but that formula works for me:

Book1
ABCDEFGHIJ
1Date1-Jan-222-Jan-223-Jan-224-Jan-22
2TypeDurationDurationDurationDurationDurationDurationDurationDuration
3Day of week71237123
4Name
5Joshua80888088
6Jannet88088808
7James88808880
8Jack08880888
Sheet8
Cell Formulas
RangeFormula
A5:E8A5=FILTER(N2:R7,LEFT(N2:N7)="J")
G5:J8G5=IF(A5="",0,SUMIFS(B5:E5,B$2:E$2,G$2,B$3:E$3,G$3))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top