SUMIF - Based On Unique Values and Multiple Criteria

C

Creation

New Member
Joined
Apr 4, 2019
Messages
15
Good evening all,



I was wondering if someone would be kind enough to help mewith the following please?


What I am trying to achieve is the following:



  • Retrieve the gross balance for all accounts (excludingduplicates where there is >1 of the same entry / account) in Column A whilstalso using Status – ‘’Open’’ (Column C) & Concept – ‘’000’’ (Column D) as ‘Criteria’to drive the overall result returned in H2.


  • Retrieve the total number of accounts (excludingduplicates where there is >1 of the same entry / account) in Column A whilstalso using Concept – ‘’000’’ (Column D) as ‘Criteria’ to drive the overallresult returned in I2.


  • Retrieve the total number of accounts ‘’Off Sale’’in Column E (excluding duplicates where there is >1 of the same entry /account in Column A) whilst also using Status – ‘’Open’’ (Column C) &Concept – ‘’000’’ (Column D) as ‘Criteria’ to drive the overall result returnedin J2.


I have been a regular viewer of these forums for year but I haveseriously struggled with trying to achieve the above. I would be really gratefulif someone with more excel knowledge and expertise could help?

P.S - Example data and format below:

A B C D E G H I J
[TABLE="width: 586"]
<colgroup><col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="64" style="width: 48pt;" span="2"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"> <col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;" span="2"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <tbody>[TR]
[TD="width: 68, bgcolor: transparent"]ACCOUNT
[/TD]
[TD="width: 64, bgcolor: transparent"]BALANCE[/TD]
[TD="width: 64, bgcolor: transparent"]STATUS[/TD]
[TD="width: 65, bgcolor: transparent"]CONCEPT[/TD]
[TD="width: 131, bgcolor: transparent"]ON SALE / OFF SALE[/TD]
[TD="width: 18, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: #DDEBF7"]Product[/TD]
[TD="width: 99, bgcolor: #DDEBF7"]Gross Balance[/TD]
[TD="width: 99, bgcolor: #DDEBF7"]Total # of A/Cs[/TD]
[TD="width: 90, bgcolor: #DDEBF7"]A/Cs Off Sale[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293746[/TD]
[TD="bgcolor: transparent"]£20.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PRODUCT X[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10394725[/TD]
[TD="bgcolor: transparent"]£30.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #DDEBF7"]Grand Total[/TD]
[TD="bgcolor: #DDEBF7"]£0.00[/TD]
[TD="bgcolor: #DDEBF7"]0[/TD]
[TD="bgcolor: #DDEBF7"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293645[/TD]
[TD="bgcolor: transparent"]£33.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]19273645[/TD]
[TD="bgcolor: transparent"]£400.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293029[/TD]
[TD="bgcolor: transparent"]£1,000.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]80974635[/TD]
[TD="bgcolor: transparent"]£90.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293029[/TD]
[TD="bgcolor: transparent"]£43.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293645[/TD]
[TD="bgcolor: transparent"]£23.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293645[/TD]
[TD="bgcolor: transparent"]£1,900.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293746[/TD]
[TD="bgcolor: transparent"]£600.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10394725[/TD]
[TD="bgcolor: transparent"]£320.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]80974635[/TD]
[TD="bgcolor: transparent"]£130.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]80974635[/TD]
[TD="bgcolor: transparent"]£500.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293746[/TD]
[TD="bgcolor: transparent"]£202.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293746[/TD]
[TD="bgcolor: transparent"]£909.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10394725[/TD]
[TD="bgcolor: transparent"]£620.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293029[/TD]
[TD="bgcolor: transparent"]£340.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293029[/TD]
[TD="bgcolor: transparent"]£505.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10293029[/TD]
[TD="bgcolor: transparent"]£9,000.00[/TD]
[TD="bgcolor: transparent"]OPEN[/TD]
[TD="bgcolor: transparent"]000[/TD]
[TD="bgcolor: transparent"]OFF SALE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


Many Thanks in advance!!

 
Sort by date Sort by votes
The formula works only when the first instance of each account meets the criteria, i.e. CONCEPT = 000 and STATUS = OPEN (as in the sample you showed),
If this does not occur in your data, the formula does not work properly.

M.
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What if the criteria (CONCEPT = 000 & STATUS OPEN) was met at random rows I.e 3rd instance (updated sample below). Would the same formula still apply? I ask because after reviewing my data set the below is probably more aligned to it

Thanks!!

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]
A
​
[/TD]
[TD="bgcolor: transparent"]
B
​
[/TD]
[TD="bgcolor: transparent"]
C
​
[/TD]
[TD="bgcolor: transparent"]
D
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]
ACCOUNT​
[/TD]
[TD="bgcolor: transparent"]
BALANCE​
[/TD]
[TD="bgcolor: transparent"]
CONCEPT​
[/TD]
[TD="bgcolor: transparent"]
STATUS​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
1​
[/TD]
[TD="bgcolor: transparent"]
10009099
​
[/TD]
[TD="bgcolor: transparent"]
-£73.00
​
[/TD]
[TD="bgcolor: transparent"]
PCA
​
[/TD]
[TD="bgcolor: transparent"]
OPEN
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]
10009099​
[/TD]
[TD="bgcolor: transparent"]
-£10.00
​
[/TD]
[TD="bgcolor: transparent"]
704
​
[/TD]
[TD="bgcolor: transparent"]
OPEN
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
3​
[/TD]
[TD="bgcolor: transparent"]
10009099
​
[/TD]
[TD="bgcolor: transparent"]
-£1789.34​
[/TD]
[TD="bgcolor: transparent"]
000​
[/TD]
[TD="bgcolor: transparent"]
OPEN​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]
4​
[/TD]
[TD="bgcolor: transparent"]
10009999
​
[/TD]
[TD="bgcolor: transparent"]
-£600.34
​
[/TD]
[TD="bgcolor: transparent"]
RCA
​
[/TD]
[TD="bgcolor: transparent"]
OPEN
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[TD="bgcolor: transparent"]

​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Possible solutions
1. G2 using a Helper column
2. H2 using an array formula


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
ACCOUNT​
[/TD]
[TD]
BALANCE​
[/TD]
[TD]
STATUS​
[/TD]
[TD]
CONCEPT​
[/TD]
[TD]
Helper​
[/TD]
[TD][/TD]
[TD]
Formula w/ Helper​
[/TD]
[TD]
Formula w/o Helper​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
10009099​
[/TD]
[TD]
-73,00​
[/TD]
[TD]
PCA​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
-1289,34​
[/TD]
[TD]
-1289,34
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
10009099​
[/TD]
[TD]
-10,00​
[/TD]
[TD]
704​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD="bgcolor: #D9D9D9"]
10009099​
[/TD]
[TD="bgcolor: #D9D9D9"]
-1789,34​
[/TD]
[TD="bgcolor: #D9D9D9"]
000​
[/TD]
[TD="bgcolor: #D9D9D9"]
OPEN​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
10009999​
[/TD]
[TD]
-600,34​
[/TD]
[TD]
RCA​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
10009099​
[/TD]
[TD]
1000,00​
[/TD]
[TD]
000​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
10458680​
[/TD]
[TD]
1000​
[/TD]
[TD]
PCA​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD="bgcolor: #D9D9D9"]
10458680​
[/TD]
[TD="bgcolor: #D9D9D9"]
500​
[/TD]
[TD="bgcolor: #D9D9D9"]
000​
[/TD]
[TD="bgcolor: #D9D9D9"]
OPEN​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
10458680​
[/TD]
[TD]
300​
[/TD]
[TD]
000​
[/TD]
[TD]
OPEN​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Helper column
Formula in E2 copied down
=IF(COUNTIFS(A$2:A2,A2,C$2:C2,"000",D$2:D2,"OPEN")=1,"x","")

Regular formula in G2
=SUMIF(E:E,"x",B:B)

Array formula in H2
=SUM(IF(FREQUENCY(IF(C2:C9="000",IF(D2:D9="OPEN",MATCH(IF(C2:C9="000",IF(D2:D9="OPEN",A2:A9)),IF(C2:C9="000",IF(D2:D9="OPEN",A2:A9)),0))),ROW(A2:A9)-ROW(A2)+1),B2:B9))
confirmed with Ctrl+Shift+Enter, not just Enter

M.
 
Last edited:
Upvote 0
[TABLE="width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]ACCOUNT
[/TD]
[TD]STATUS
[/TD]
[TD]BALANCE
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]10102030
[/TD]
[TD]NR
[/TD]
[TD]£0.00

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10102030
[/TD]
[TD]NR
[/TD]
[TD]£50.00

[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20304050
[/TD]
[TD]NR
[/TD]
[TD]£1.00

[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]20304050
[/TD]
[TD]NR
[/TD]
[TD]£25.00

[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]70102030
[/TD]
[TD]NR
[/TD]
[TD]£0.00

[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]70102030
[/TD]
[TD]NR
[/TD]
[TD]£88.90

[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]18301011
[/TD]
[TD]NR
[/TD]
[TD]£4.55

[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]18301011
[/TD]
[TD]NR
[/TD]
[TD]£150.33

[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]13201155
[/TD]
[TD]NR
[/TD]
[TD]£0.00

[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]13201155
[/TD]
[TD]NR
[/TD]
[TD]£10.00

[/TD]
[/TR]
</tbody>[/TABLE]

Hi Marcelo,

First off I want to apologize for not replying sooner. I have been very unwell and only just made a full recovery. I also want to thank you for your last post as option 2 worked and has really helped me a great deal!!!

However, I was wondering if you could help with me a similar issue (example table above). What I am trying to accomplish is to ascertain a total balance (£324.23) for the accounts and balances in rows 2,4,6,8 & 10 only whilst also ensuring the status (NR) is incorporated into the formula.

I have been trying for 2 days without any success and would be so grateful if you could please help me?

Thank you!!!
 
Last edited:
Upvote 0
Question
Are there always two instances of each account, both with STATUS = NR, or is possible one or three instances with different STATUS?

Do not oversimplify the example. Try to provide a sample of data that includes all possible situations along with the expected result. Otherwise, a solution may not work in your actual case.

M.
 
Upvote 0
h
Marcelo Branco said:
Assuming the values in column CONCEPT (000) are texts, not numbers, maybe something like this



A​
B​
C​
D​
E​
F​
G​
H​
I​
1​
ACCOUNT​
BALANCE​
STATUS​
CONCEPT​
ON SALE / OFF SALE​
Question1​
Question2​
Question3​
2​
10293746​
5000,00​
OPEN​
000​
OFF SALE​
10550​
5​
4​
3​
10293746​
5000,00​
OPEN​
000​
OFF SALE​
4​
10829098​
990,00​
OPEN​
000​
OFF SALE​
5​
10829098​
990,00​
OPEN​
000​
OFF SALE​
6​
10227890​
4000,00​
OPEN​
000​
OFF SALE​
7​
10227890​
4000,00​
OPEN​
000​
OFF SALE​
8​
10458680​
560,00​
OPEN​
000​
OFF SALE​
9​
10458680​
560,00​
OPEN​
000​
OFF SALE​
10​
10809094​
0,00​
OPEN​
000​
ON SALE​
11​
10809094​
0,00​
OPEN​
000​
ON SALE​

Formula in G2
=SUMPRODUCT(--(C2:C11="OPEN"),--(D2:D11="000"),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1),B2:B11)

Formula in H2
=SUMPRODUCT(--(C2:C11="OPEN"),--(D2:D11="000"),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1))

Formula in I2
=SUMPRODUCT(--(C2:C11="OPEN"),--(D2:D11="000"),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1),--(E2:E11="OFF SALE"))

M.
Click to expand...
Hi, does this work in google sheet? I tried this formula and works at first, I changed the “ open”and “000” to changing variables, but even so, every time the column A gets a duplicate, it disregards other variables like of column C and D.

:(( is there a way to do this in google sheet?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
989
AlanY
A
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
M
  • Question Question
Index Match formula with multiple criteria
Replies
5
Views
781
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

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