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]

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.

 

[Fluff]

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.

 

[Creation]

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

 

[Creation]

Sorry my error. Please accept my apologies

 

[Marcelo Branco]

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

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

M.

 

[Creation]

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


[TABLE="width: 389"]
<tbody>[TR]
[TD="width: 84, bgcolor: transparent"]ACCOUNT
[/TD]
[TD="width: 80, bgcolor: transparent"]BALANCE
[/TD]
[TD="width: 69, bgcolor: transparent"]STATUS
[/TD]
[TD="width: 81, bgcolor: transparent"]CONCEPT
[/TD]
[TD="width: 205, bgcolor: transparent"]ON SALE / OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10293746
[/TD]
[TD="width: 80, bgcolor: transparent"]£5,000.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10293746
[/TD]
[TD="width: 80, bgcolor: transparent"]£5,000.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10829098
[/TD]
[TD="width: 80, bgcolor: transparent"]£990.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10829098
[/TD]
[TD="width: 80, bgcolor: transparent"]£990.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10227890
[/TD]
[TD="width: 80, bgcolor: transparent"]£4,000.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10227890
[/TD]
[TD="width: 80, bgcolor: transparent"]£4,000.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10458680
[/TD]
[TD="width: 80, bgcolor: transparent"]£560.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10458680
[/TD]
[TD="width: 80, bgcolor: transparent"]£560.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]OFF SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10809094
[/TD]
[TD="width: 80, bgcolor: transparent"]£0.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]ON SALE
[/TD]
[/TR]
[TR]
[TD="width: 84, bgcolor: transparent"]10809094
[/TD]
[TD="width: 80, bgcolor: transparent"]£0.00
[/TD]
[TD="width: 69, bgcolor: transparent"]OPEN
[/TD]
[TD="width: 81, bgcolor: transparent"]000
[/TD]
[TD="width: 205, bgcolor: transparent"]ON SALE
[/TD]
[/TR]
</tbody>[/TABLE]

Thank You!

Sam

 

[Marcelo Branco]

Assuming the values in column CONCEPT (000) are texts, not numbers, maybe something like this


[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][td="bgcolor: #DCE6F1"]

G​

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

H​

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

I​

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

1​

[/td][td]

ACCOUNT​

[/td][td]

BALANCE​

[/td][td]

STATUS​

[/td][td]

CONCEPT​

[/td][td]

ON SALE / OFF SALE​

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

Question1​

[/td][td]

Question2​

[/td][td]

Question3​

[/td][/tr]

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

2​

[/td][td]

10293746​

[/td][td]

5000,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

10550​

[/td][td]

5​

[/td][td]

4​

[/td][/tr]

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

3​

[/td][td]

10293746​

[/td][td]

5000,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

4​

[/td][td]

10829098​

[/td][td]

990,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

5​

[/td][td]

10829098​

[/td][td]

990,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

6​

[/td][td]

10227890​

[/td][td]

4000,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

7​

[/td][td]

10227890​

[/td][td]

4000,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

8​

[/td][td]

10458680​

[/td][td]

560,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

9​

[/td][td]

10458680​

[/td][td]

560,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

OFF SALE​

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

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

10​

[/td][td]

10809094​

[/td][td]

0,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

ON SALE​

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

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

11​

[/td][td]

10809094​

[/td][td]

0,00​

[/td][td]

OPEN​

[/td][td]

000​

[/td][td]

ON SALE​

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

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.

 

[Creation]

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.

 

[Creation]

Hi Marcelo,

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


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