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

 

[Marcelo Branco]

Are you saying to count (or add) the rows whose STATUS column contains OPEN or PENDING?
Or should the rows contain the text OPEN & PENDING?

M.

 

[Creation]

Sorry should have clarified, i am saying to “add” the rows whose STATUS column contains OPEN & PENDING

Also, in relation to the first formula; if the CONCEPT column (000) is not formatted to text do you have any ideas how of I overcome the issue of the formula result returning nothing? I’ve been trying since this morning with no joy.

Ideally I need to the source data to stay in the format it it is. The reason for this is because the data extract I am working with has been imported from SQL into excel and converting the range or changing the format would result in the existing connections being lost, and the ability for the data to be refreshed would be lost. Also the format would revert back to its original upon the refresh of the data each morning.

Thank you once again!

 

[Marcelo Branco]

Check if column D (CONCEPT) contains number - try in an empty column
=ISNUMBER(D2)
and copy down

If the values are numbers try
=SUMPRODUCT(--(C2:C11="OPEN & PENDING"),--(D2:D11=0),--(MATCH(A2:A11,A2:A11,0)=ROW(A2:A11)-ROW(A2)+1),B2:B11)

M.

 

[Creation]

Thanks for the suggestion! I fixed it by doing the following:

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

The only thing I am still having trouble with is the CONCEPT ''000''. I did as you suggested and none of the values are numbers (the =ISNUMBER(D2) came back as false).

Any other ideas or suggestions matey?

Thanks!!

 

[Marcelo Branco]

Maybe...

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

M.

 

[Creation]

Thank for the above, I thought that would work but no joy.

I think I have narrowed it down to the match function of the formula: ,--(MATCH('Retail Products'!C2:C6753,'Retail Products'!C2:C6753,0)=ROW('Retail Products'!C2:C6753)-ROW('Retail Products'!C2)+1)

If I remove the above section from the formula and only keep: =SUMPRODUCT(--('Retail Products'!O2:O6753="OPEN"),--('Retail Products'!N2:N6753="000"),'Retail Products'!I2:I6753) the result I get is the full balance sum including duplicates based on STATUS = OPEN & CONCEPT = 000 (*At least we can rule out the formatting of the CONCEPT column is not the issue).

So in theory I am halfway there, I just need a way to report the full balance sum based on STATUS = OPEN, CONCEPT = 000 for unique accounts only. Is there anyway the formula can be amended?

Currently the full formula looks something like this: =SUMPRODUCT(--('Retail Products'!O2:O6753="OPEN"),--('Retail Products'!N2:N6753="000"),--(MATCH('Retail Products'!C2:C6753,'Retail Products'!C2:C6753,0)=ROW('Retail Products'!C2:C6753)-ROW('Retail Products'!C2)+1),'Retail Products'!I2:I6753)

The above is my live example from my actual document: O = STATUS, N = CONCEPT, C= ACCOUNT, I= BALANCE

Thank you once again for your support - I really appreciate it.

We will get there!! haha

 

[Marcelo Branco]

To find out the problem we need a new sample of data along with the expected results.

M.

 

[Creation]

[TABLE="width: 375"]
<tbody>[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]

C​

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

I​

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

N​

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

O​

[/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"]

000​

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

OPEN​

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

2​

[/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"]

3​

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

20890078​

[/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"]

20890078​

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

-£1789.34​

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

343​

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

OPEN​

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

5​

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

30457090​

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

-£2010.10​

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

000​

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

OPEN​

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

6​

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

30457090​

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

-£2010.10​

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

PCI​

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

OPEN​

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

7​

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

20011003​

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

-£1500.90​

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

000​

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

OPEN​

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

8​

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

20011003​

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

-£1500.90​

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

968​

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

OPEN​

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

---

---

​

I need a way toreport the full balance sum based on STATUS = OPEN, CONCEPT = 000 for uniqueaccount entries only.

Expected result would be a total sum of -£5,373.34 (for those rows highlighted)

As it stands the data in the concept column is not stored as numbers (I checkedthis based on your advice using the =ISNUMBER formula).

In your original reply the MATCH function worked perfectly but it didnot work for me. When I removed the MATCH function it did work (as per myprevious reply) but summed all of the balances including theduplicate entries - I need it do unique entries only.

Hope this new data sample is better?

Thanks!!

 

[Marcelo Branco]

The formula I've suggested worked perfectly.


[Table="class: grid"][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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

ACCOUNT​

[/td][td]

BALANCE​

[/td][td]

CONCEPT​

[/td][td]

STATUS​

[/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

10009099​

[/td][td]

-73,00​

[/td][td]

000​

[/td][td]

OPEN​

[/td][td][/td][td]

-5373,34​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

10009099​

[/td][td]

-73,00​

[/td][td]

PCA​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

20890078​

[/td][td]

-1789,34​

[/td][td]

000​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

20890078​

[/td][td]

-1789,34​

[/td][td]

343​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

30457090​

[/td][td]

-2010,10​

[/td][td]

000​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

30457090​

[/td][td]

-2010,10​

[/td][td]

PCI​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

20011003​

[/td][td]

-1500,90​

[/td][td]

000​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

20011003​

[/td][td]

-1500,90​

[/td][td]

968​

[/td][td]

OPEN​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in F2
=SUMPRODUCT(--(C2:C9="000"),--(D2:D9="OPEN"),--(MATCH(A2:A9,A2:A9,0)=ROW(A2:A9)-ROW(A2)+1),B2:B9)

M.

 

[Creation]

Yes correct. It works perfectly for the sample data but when I apply it to my source data set (which is very similar to the sample data) it does not work - I just get £0.00. Funny thing is if I remove the MATCH function from the formula then I get the total balance sum including duplicate accounts entries. Absolutely lost