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
ACCOUNT
BALANCESTATUSCONCEPTON SALE / OFF SALEProductGross BalanceTotal # of A/CsA/Cs Off Sale
10293746£20.00OPEN000OFF SALEPRODUCT X
10394725£30.00OPEN000OFF SALEGrand Total£0.0000
10293645£33.00OPEN000OFF SALE
19273645£400.00OPEN000OFF SALE
10293029£1,000.00OPEN000OFF SALE
80974635£90.00OPEN000OFF SALE
10293029£43.00OPEN000OFF SALE
10293645£23.00OPEN000OFF SALE
10293645£1,900.00OPEN000OFF SALE
10293746£600.00OPEN000OFF SALE
10394725£320.00OPEN000OFF SALE
80974635£130.00OPEN000OFF SALE
80974635£500.00OPEN000OFF SALE
10293746£202.00OPEN000OFF SALE
10293746£909.00OPEN000OFF SALE
10394725£620.00OPEN000OFF SALE
10293029£340.00OPEN000OFF SALE
10293029£505.00OPEN000OFF SALE
10293029£9,000.00OPEN000OFF 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!!

 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Welcome to Mr Excel forum

I'm not sure i understand what you're looking for.
Regarding the data sample above what would be the expected/desired results? (logic to achieve them?)

M.
 
Upvote 0
Cross posted https://www.excelforum.com/excel-fo...d-on-unique-values-and-multiple-criteria.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi Marcelo, thank you for your reply - I greatly appreciate it. Sorry, the original post came out really weird, what I am trying to accomplish is:

- Retrieve the gross balance for all accounts (excluding duplicates where there is >1 of the same entry / account) whilst also using Status – ''Open'' & Concept – ''000'' as the SUMIF criteria to drive the overall result returned.

- Retrieve the total number of accounts (excluding duplicates where there is >1 of the same entry / account) whilst also using Concept – ''000'' as the SUMIF criteria to drive the overall result returned.

- Retrieve the total number of accounts ''Off Sale'' in (excluding duplicates where there is >1 of the same entry / account in the account column) whilst also using Status – ''Open'' & Concept – ''000'' as the SUMIF criteria to drive the overall result returned.

I suppose it's essentially a SUMIF formula at it's core. However, the main function is to only SUMIF based on unique account number values using multiple criteria.

Hope this clarifies it a little more for you?

Thanks again!!!!
 
Upvote 0
Creation said:
- Retrieve the gross balance for all accounts (excluding duplicates where there is >1 of the same entry / account) whilst also using Status – ''Open'' & Concept – ''000'' as the SUMIF criteria to drive the overall result returned.
Click to expand...

hmm... not clear at least for me

How the formula should handle entries like?

10293746 £202.00 OPEN 000 OFF SALE
10293746 £909.00 OPEN 000 OFF SALE

and

10293029 £340.00 OPEN 000 OFF SALE
10293029 £505.00 OPEN 000 OFF SALE
10293029 £9,000.00 OPEN 000 OFF SALE

Ignore them? Consider only the first record? Not clear:confused:

M.
 
Upvote 0
Good morning Marcelo,

I slept on my query from yesterday and realised it probably was not very well explained. I have therefore amended my original query andtried to simplify it more below with expected results. I would be so grateful if could take a look and let me know if it’s something you can please help with?

Ideally what I need the SUMIF to do is identify the duplicate entries in the ‘Account Column’ and after doing so retrieve the total gross balance for unique accounts based on the following criteria: ‘’Open’’& ‘’000’’
Expected result would be £10,550.00

Secondly, I require the TOTAL number of open accounts (similar to above) - it would need to identify the duplicate entries in the ‘Account Column’and after doing so retrieve the total number of unique accounts based on the following criteria: ‘’Open’’, ‘’000’’ & “Off Sale”
Expected result would be 5

Lastly, I require the TOTAL number of accounts Off Sale (again, similar to above) – it would need identify the duplicate entries in the‘Account Column’ and after doing so retrieve the total number of unique accounts based on the following criteria: ‘’Open’’, ‘’000’’ & “Off Sale”
Expected result would be 4


ACCOUNT
BALANCE
STATUS
CONCEPT
ON SALE / OFF SALE
10293746
£5,000.00
OPEN
000
OFF SALE
10293746
£5,000.00
OPEN
000
OFF SALE
10829098
£990.00
OPEN
000
OFF SALE
10829098
£990.00
OPEN
000
OFF SALE
10227890
£4,000.00
OPEN
000
OFF SALE
10227890
£4,000.00
OPEN
000
OFF SALE
10458680
£560.00
OPEN
000
OFF SALE
10458680
£560.00
OPEN
000
OFF SALE
10809094
£0.00
OPEN
000
ON SALE
10809094
£0.00
OPEN
000
ON SALE

<tbody>
</tbody>

Thank You!

Sam
 
Upvote 0
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.
 
Upvote 0
Wow Marcelo! I think you’ve hit the nail on the head. I will not be able to test this fully until Monday but when I do first thing I will be sure to let you know. Thank you for taking time to review this - please keep your fingers crossed for me. Thank you.
 
Upvote 0
Hi Marcelo,

I have tested your formula's below and success they work!!! Thank You so much mate :biggrin:


I have one question though, say if the source data changed slightly so that the status column included OPEN & PENDING. Is there anyway I could amend the following formula to include OPEN & PENDING together?

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

Would the above work at all mate?

Thanks,
Sam
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit
S
  • Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
870
AlanY
A
H
  • Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
5K
Fluff
Fluff
M
  • Question
Index Match formula with multiple criteria
Replies
5
Views
664
DanteAmor
DanteAmor
L
  • Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
1K
MickG
M

Forum statistics

Threads
1,214,971
Messages
6,122,520
Members
449,088
Latest member
RandomExceller01

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