JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
Have a 5 column data table, columns 1-3 string values, columns 4-5 numerical values and each row is a unique record.
e.g.
a, b, c, 100, 4
d, e, f, 200, 20
On another sheet, I use two SUMIFS formula's to return values from column 4 and column 5 into (e.g.) columns A and B
(1)
(2)
I'd like a formula like:=[SUM(1)-SUM(2)]/SUM(1), without helper columns or showing formula (2) results anywhere (no hidden column)
I tried an array formula but result is not correct:
Can someone suggest suitable formula?
TIA,
Jack
Have a 5 column data table, columns 1-3 string values, columns 4-5 numerical values and each row is a unique record.
e.g.
a, b, c, 100, 4
d, e, f, 200, 20
On another sheet, I use two SUMIFS formula's to return values from column 4 and column 5 into (e.g.) columns A and B
(1)
Rich (BB code):
=SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,N1,Data!$N$5:$N$146,O1,Data!$O$5:$O$146,P1)
(2)
Rich (BB code):
=SUMIFS(Data!$Q$5:$Q$146,Data!$M$5:$M$146,N1,Data!$N$5:$N$146,O1,Data!$O$5:$O$146,P1)
I'd like a formula like:=[SUM(1)-SUM(2)]/SUM(1), without helper columns or showing formula (2) results anywhere (no hidden column)
I tried an array formula but result is not correct:
Rich (BB code):
={(SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4)-SUMIFS(Data!$Q$5:$Q$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4))/SUMIFS(Data!$P$5:$P$146,Data!$M$5:$M$146,S1:S4,Data!$N$5:$N$146,T1:T4,Data!$O$5:$O$146,U1:U4)}
Can someone suggest suitable formula?
TIA,
Jack