Non Blank Matches

[Legacy 436357]

Hello,

Is there a formula to retrieve non blank matches in column D?

Thank you

Excel Workbook
	A	B	C	D
1	ID	Sales Order	Unique IDs	Sales Order
2	10		10	matching Sales Order Numbers
3	10	50	20	matching Sales Order Numbers
4	10			matching Sales Order Numbers
5	20			matching Sales Order Numbers
6	20			matching Sales Order Numbers
7	20	100		matching Sales Order Numbers
8	20			matching Sales Order Numbers
9	20			matching Sales Order Numbers
10	20			matching Sales Order Numbers
Sheet3

 

[Marcelo Branco]

Maybe...

D2 copied down
=IF(C2="","",SUMIF(A:A,C2,B:B))

M.

 

[DanteAmor]

You can also check the possibility of a pivot table; it creates the unique IDs and their corresponding Sums in aumatmatic.

 

[Legacy 436357]

Thank you that works great!!!

 

[Legacy 436357]

What if from the unique values in column C there aren't any matches in column B?

In this case column D will have some blanks.

Can the formula adjust for that?

 

[Marcelo Branco]

What if from the unique values in column C there aren't any matches in column B?

In this case column D will have some blanks.

Can the formula adjust for that?

In this case the formula returns 0, not blank. Is it Ok?

M.

 

[Legacy 436357]

I would rather have the column C sorted so that column D have all the values starting in D2. I will be hiding column C for this worksheet.

Thank you so much

 

[Marcelo Branco]

You're welcome.

M.

 

[Legacy 436357]

Marcelo,

Can column D have the values starting at cell D2 so there aren't blanks in the range? I provided a small sample of data.

 

[Marcelo Branco]

Using your data sample: formula in post 2 does it


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

1​

[/td][td]

ID​

[/td][td]

Sales Order​

[/td][td]

Unique IDs​

[/td][td]

Sales Order​

[/td][/tr]

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

2​

[/td][td]

10​

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

10​

[/td][td]

50​

[/td][/tr]

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

3​

[/td][td]

10​

[/td][td]

50​

[/td][td]

20​

[/td][td]

100​

[/td][/tr]

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

4​

[/td][td]

10​

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

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

5​

[/td][td]

20​

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

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

6​

[/td][td]

20​

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

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

7​

[/td][td]

20​

[/td][td]

100​

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

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

8​

[/td][td]

20​

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

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

9​

[/td][td]

20​

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

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

10​

[/td][td]

20​

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

Formula in D2 copied down
=IF(C2="","",SUMIF(A:A,C2,B:B))

M.