SUMIF - Based On Unique Values and Multiple Criteria

[Creation]

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

ACCOUNT	BALANCE	STATUS	CONCEPT	ON SALE / OFF SALE		Product	Gross Balance	Total # of A/Cs	A/Cs Off Sale
10293746	£20.00	OPEN	000	OFF SALE		PRODUCT X
10394725	£30.00	OPEN	000	OFF SALE		Grand Total	£0.00	0	0
10293645	£33.00	OPEN	000	OFF SALE
19273645	£400.00	OPEN	000	OFF SALE
10293029	£1,000.00	OPEN	000	OFF SALE
80974635	£90.00	OPEN	000	OFF SALE
10293029	£43.00	OPEN	000	OFF SALE
10293645	£23.00	OPEN	000	OFF SALE
10293645	£1,900.00	OPEN	000	OFF SALE
10293746	£600.00	OPEN	000	OFF SALE
10394725	£320.00	OPEN	000	OFF SALE
80974635	£130.00	OPEN	000	OFF SALE
80974635	£500.00	OPEN	000	OFF SALE
10293746	£202.00	OPEN	000	OFF SALE
10293746	£909.00	OPEN	000	OFF SALE
10394725	£620.00	OPEN	000	OFF SALE
10293029	£340.00	OPEN	000	OFF SALE
10293029	£505.00	OPEN	000	OFF SALE
10293029	£9,000.00	OPEN	000	OFF SALE

<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> </tbody>

Many Thanks in advance!!

 

[Marcelo Branco]

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.

 

[Creation]

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!!

	A ​	B ​	C ​	D ​
	ACCOUNT​	BALANCE​	CONCEPT​	STATUS​
1​	10009099 ​	-£73.00 ​	PCA ​	OPEN ​
2​	10009099​	-£10.00 ​	704 ​	OPEN ​
3​	10009099 ​	-£1789.34​	000​	OPEN​
4​	10009999 ​	-£600.34 ​	RCA ​	OPEN ​
​	​	​	​	​
​	​	​	​	​
​	​	​	​	​
​	​	​	​	​

<tbody>
</tbody>

 

[Marcelo Branco]

Possible solutions
1. G2 using a Helper column
2. H2 using an array formula

	A​	B​	C​	D​	E​	F​	G​	H​
1​	ACCOUNT​	BALANCE​	STATUS​	CONCEPT​	Helper​		Formula w/ Helper​	Formula w/o Helper​
2​	10009099​	-73,00​	PCA​	OPEN​			-1289,34​	-1289,34 ​
3​	10009099​	-10,00​	704​	OPEN​
4​	10009099​	-1789,34​	000​	OPEN​	x​
5​	10009999​	-600,34​	RCA​	OPEN​
6​	10009099​	1000,00​	000​	OPEN​
7​	10458680​	1000​	PCA​	OPEN​
8​	10458680​	500​	000​	OPEN​	x​
9​	10458680​	300​	000​	OPEN​
10​

<tbody>
</tbody>

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.

 

[Creation]

	ACCOUNT	STATUS	BALANCE
1	10102030	NR	£0.00
2	10102030	NR	£50.00
3	20304050	NR	£1.00
4	20304050	NR	£25.00
5	70102030	NR	£0.00
6	70102030	NR	£88.90
7	18301011	NR	£4.55
8	18301011	NR	£150.33
9	13201155	NR	£0.00
10	13201155	NR	£10.00

<tbody>
</tbody>

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!!!

 

[Marcelo Branco]

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.

 

[bronxjm]

h

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.

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?