Formula - Sumif(s)? Sumproduct? etc.

F

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,643
Office Version
  1. 365
Platform
  1. Windows
I am looking for a formula to produce the results in column P.

XCash Summary.xlsm
FGHIJKLMNOPQ
1
28 Apr 25HKD1.0000108,099
315 Oct 24USD1.00006,407
420 Jan 25CNY1.0000535
514 Oct 24HKD1.000031,571
610 Mar 25HKD35,901
79 Dec 24HKD1.000042,575
827 Feb 25HKD1.000054,444
93 Jan 25HKD1.000031,095
1017 Jan 25HKD1.000085,032
1121 Jan 25HKD1.000051,438
1218 Feb 25HKD86,394
1321 Feb 25HKD1.0000170,065
143 Oct 24USD1.000011,841
1529 Aug 25USD1.000010,057
1630 Jun 25HKD1.000096,652
1730 Sep 24USD1.000055,892
1820 Aug 25USD1.000014,348
19
2055,89224 09 USD55,892 USD Sep 24
2131,57124 10 HKD31,571 HKD Oct 24
2218,24824 10 USD18,248 USD Oct 24
2342,57524 12 HKD42,575 HKD Dec 24
2453525 01 CNY535 CNY Jan 25
25167,56525 01 HKD167,565 HKD Jan 25
26224,50925 02 HKD224,509 HKD Feb 25
27108,09925 04 HKD108,099 HKD Apr 25
2896,65225 06 HKD96,652 HKD Jun 25
2924,40525 08 USD24,405 USD Aug 25
Summary
Cell Formulas
RangeFormula
N20:N29N20=SORT(UNIQUE(FILTER(TEXT(F$1:F$19, "yy mm ")&H$1:H$19, J$1:J$19 <> "")))
Q20:Q29Q20=--SORT(LEFT((UNIQUE(FILTER(DATE(YEAR(F$1:F$19),MONTH(F$1:F$19),1)&H$1:H$19,J$1:J$19<>""))),5))
M20:M29M20=SUMPRODUCT((J$1:J$19<>"")*(TEXT(F$1:F$19,"yy mm ")&H$1:H$19=N20)*L$1:L$19)
Dynamic array formulas.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi,

if I understood well, do you mean like this ?

Book1
ABCDEFGHIJKLMNOPQR
1
245755HKD1108099
345580USD16407
445677CNY1535
545579HKD131571
645726HKD35901
745635HKD142575
845715HKD154444
945660HKD131095
1045674HKD185032
1145678HKD151438
1245706HKD86394
1345709HKD1170065
1445568USD111841
1545898USD110057
1645838HKD196652
1745565USD155892
1845889USD114348
19
205589224 09 USD55892 USDSep-24
213157124 10 HKD31571 HKDOct-24
221824824 10 USD18248 USDOct-24
234257524 12 HKD42575 HKDDec-24
2453525 01 CNY535 CNYJan-25
2516756525 01 HKD167565 HKDJan-25
2622450925 02 HKD224509 HKDFeb-25
2710809925 04 HKD108099 HKDApr-25
289665225 06 HKD96652 HKDJun-25
292440525 08 USD24405 USDAug-25
30
Sheet1
Cell Formulas
RangeFormula
N20:N29N20=SORT(UNIQUE(FILTER(TEXT(F$1:F$19, "yy mm ")&H$1:H$19, J$1:J$19 <> "")))
Q20:Q29Q20=--SORT(LEFT((UNIQUE(FILTER(DATE(YEAR(F$1:F$19),MONTH(F$1:F$19),1)&H$1:H$19,J$1:J$19<>""))),5))
M20:M29M20=SUMPRODUCT((J$1:J$19<>"")*(TEXT(F$1:F$19,"yy mm ")&H$1:H$19=N20)*L$1:L$19)
P20:P29P20=M20&" "&RIGHT(N20,4)
Dynamic array formulas.
 
Upvote 0
Assuming you have other columns filled, wouldn't it be just:
Excel Formula: 
=TEXT(M20;"0,000")& RIGHT(N20;4)
and copied down?
 
Upvote 0
Kaper said:
Assuming you have other columns filled, wouldn't it be just:
Excel Formula: 
=TEXT(M20;"0,000")& RIGHT(N20;4)
and copied down?
Click to expand...
Yes, but I should have mentioned that I want to eliminate columns M:N
 
Upvote 0
ah sorry, I realise now what you're trying to do - apologies for misunderstanding the question...
 
Upvote 0
With 365, you could consider Pivotby or Groupby

Book1
FGHLMNOPQR
1
245755HKD108099 CNYHKDUSDTotal
345580USD640724 Sep55,89255,892
445677CNY53524 Oct11,84111,841
545579HKD3157124 Oct31,57131,571
645726HKD3590124 Oct6,4076,407
745635HKD4257524 Dec42,57542,575
845715HKD5444425 Jan31,09531,095
945660HKD3109525 Jan85,03285,032
1045674HKD8503225 Jan535535
1145678HKD5143825 Jan51,43851,438
1245706HKD8639425 Feb86,39486,394
1345709HKD17006525 Feb170,065170,065
1445568USD1184125 Feb54,44454,444
1545898USD1005725 Mar35,90135,901
1645838HKD9665225 Apr108,099108,099
1745565USD5589225 Jun96,65296,652
1845889USD1434825 Aug14,34814,348
1925 Aug10,05710,057
20Total535793,26698,545892,346
21
Sheet1
Cell Formulas
RangeFormula
N2:R20N2=PIVOTBY(F2:F18,H2:H18,L2:L18,SUM)
Dynamic array formulas.
 
Upvote 0
Another option if you have groupby is
Fluff.xlsm
FGHIJKLMNOP
1
208/04/2025HKD1108099Sep-24USD55892
315/10/2024USD16407Oct-24HKD31571
420/01/2025CNY1535Oct-24USD18248
514/10/2024HKD131571Dec-24HKD42575
610/03/2025HKD35901Jan-25CNY535
709/12/2024HKD142575Jan-25HKD167565
827/02/2025HKD154444Feb-25HKD224509
903/01/2025HKD131095Apr-25HKD108099
1017/01/2025HKD185032Jun-25HKD96652
1121/01/2025HKD151438Aug-25USD24405
1218/02/2025HKD86394
1321/02/2025HKD1170065
1403/10/2024USD111841
1529/08/2025USD110057
1630/06/2025HKD196652
1730/09/2024USD155892
1820/08/2025USD114348
Data
Cell Formulas
RangeFormula
N2:P11N2=GROUPBY(FILTER(HSTACK(DATE(YEAR(F2:F18),MONTH(F2:F18),1),H2:H18),J2:J18<>""),FILTER(L2:L18,J2:J18<>""),SUM,,0)
Dynamic array formulas.
 
Upvote 0
In post #7, I did not include the column J information.

Use Fluff's suggestion.

An alternative that uses a helper column for the Date follows

Book1.xlsx
FGHIJKLMNOP
1DateCountryIncludeAmount
230-Sep-241-Sep-24USD15589224 SepUSD55,892
33-Oct-241-Oct-24USD11184124 OctHKD31,571
414-Oct-241-Oct-24HKD13157124 OctUSD18,248
515-Oct-241-Oct-24USD1640724 DecHKD42,575
69-Dec-241-Dec-24HKD14257525 JanCNY535
73-Jan-251-Jan-25HKD13109525 JanHKD167,565
817-Jan-251-Jan-25HKD18503225 FebHKD224,509
920-Jan-251-Jan-25CNY153525 AprHKD108,099
1021-Jan-251-Jan-25HKD15143825 JunHKD96,652
1118-Feb-251-Feb-25HKD8639425 AugUSD24,405
1221-Feb-251-Feb-25HKD1170065Total770,051
1327-Feb-251-Feb-25HKD154444
1410-Mar-251-Mar-25HKD35901
158-Apr-251-Apr-25HKD1108099
1630-Jun-251-Jun-25HKD196652
1720-Aug-251-Aug-25USD114348
1829-Aug-251-Aug-25USD110057
19
Sheet2
Cell Formulas
RangeFormula
N2:P12N2=GROUPBY(G2:H18,L2:L18,SUM,,1,1,L2:L18*J2:J18>0)
G2:G18G2=F2-DAY(F2)+1
Dynamic array formulas.
 
Upvote 0
Fluff / Dave Patton
Thanks for your suggestions. Unfortunately, I can’t add any columns (sorry, I should have explained that).
I have revised Fluff’s formula to :
Excel Formula: 
=GROUPBY(FILTER(HSTACK(TEXT(DATE(YEAR(F2:F18),MONTH(F2:F18),1),"mmm yy ")&H2:H18),J2:J18<>""),FILTER(L2:L18,J2:J18<>""),SUM,,0)
This formula works. Many thanks!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
810
Cubist
Cubist
V
  • Question Question
SUM XLOOKUP
Replies
5
Views
195
StephenCrump
S
O
  • Question Question
Graph to view comparison of monthly changes to numbers
Replies
0
Views
152
OJMcGee
O
S
  • Solved
lookup return from range of dates
Replies
4
Views
492
Peter_SSs
Peter_SSs
V
  • Question Question
Toggle Feature on Summary
Replies
3
Views
234
myall_blues
myall_blues

Forum statistics

Threads
1,222,903
Messages
6,168,939
Members
452,227
Latest member
sam1121

Share this page

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