SUMIF, VLOOKUP, and #N/A?

[tiffanyfit3d]

Hi all!

First time to the forum but I've got a doozy of a problem and can't seem to find any answers. Hoping you guys can help!

I have Table A with a bunch of invoice numbers and quantities. I have Table B that also has invoice numbers on it. There are some entries only in Table A, some only in Table B, and some in both. I need a formula that will sum the quantities of invoice numbers that are ONLY in Table A.

Example:

Table A

Inv#	Qty
1065	3
1077	1
1083	5
1086	2

Table B

Inv#

1069

1077

1083

1090

Result: 5

I know that I could add a third column to Table A and do a vlookup and then sumif on #N/A but I want this to just be a template where I can paste my data and the result spits out.

Any thoughts?

Thanks in advance!

-Tiffany

 

[mole999]

your post was automoderated, and as such the system would have sent you an email to that effect. no one can repeat post

 

[Aladin Akyurek]

Let A:B of Sheet1 house table 1 and A:A of Sheet2 table 2.

Somewhere convenient in Sheet1 run the following formula:

=SUMPRODUCT(--ISNA(MATCH($A$2:$A$5,Sheet2!$A$2:$A$5,0)),$B$2:$B$5)

 

[tiffanyfit3d]

Let A:B of Sheet1 house table 1 and A:A of Sheet2 table 2.

Somewhere convenient in Sheet1 run the following formula:

=SUMPRODUCT(--ISNA(MATCH($A$2:$A$5,Sheet2!$A$2:$A$5,0)),$B$2:$B$5)

Ah yes! This works! Yay, thank you!

Is there any way to add extra conditionals? If I have Salesperson listed in Column C, can I make the formula only add up for an individual Salesperson as well?

 

[tiffanyfit3d]

Ah yes! This works! Yay, thank you!

Is there any way to add extra conditionals? If I have Salesperson listed in Column C, can I make the formula only add up for an individual Salesperson as well?

Ok, so I figured out how to get my Salesperson conditional input. One LAST thing is that some of my Salesperson entries have multiple salespeople (i.e. "John", "Jane", "John/Jane") so I thought I would just be able to use asterisks to get both John and John/Jane to add up. This is the formula I thought should work but it is returning zero.

=SUMPRODUCT(--ISNA(MATCH($A$2:$A$5,Sheet2!$A$2:$A$5,0)),$B$2:$B$5,,--($C$2:$C$999="*John*")

Any thoughts?

 

[Aladin Akyurek]

Ok, so I figured out how to get my Salesperson conditional input. One LAST thing is that some of my Salesperson entries have multiple salespeople (i.e. "John", "Jane", "John/Jane") so I thought I would just be able to use asterisks to get both John and John/Jane to add up. This is the formula I thought should work but it is returning zero.

=SUMPRODUCT(--ISNA(MATCH($A$2:$A$5,Sheet2!$A$2:$A$5,0)),$B$2:$B$5,,--($C$2:$C$999="*John*")

Any thoughts?

Perhaps...

=SUMPRODUCT($B$2:$B$999,--ISNA(MATCH($A$2:$A$999,Sheet2!$A$2:$A$5,0)),--ISNUMBER(SEARCH("john",$C$2:$C$999)))

 

[tiffanyfit3d]

Perhaps...

=SUMPRODUCT($B$2:$B$999,--ISNA(MATCH($A$2:$A$999,Sheet2!$A$2:$A$5,0)),--ISNUMBER(SEARCH("john",$C$2:$C$999)))

This worked exactly as I needed it to! Thank you so much, Aladin!!

 

[Aladin Akyurek]

This worked exactly as I needed it to! Thank you so much, Aladin!!

You are welcome.