I'm pretty sure this can be done with sumproduct, but I can't quite get my
head around it.

I have two tables, Tbl1 A & B, Tbl2 E & F. Both are limited to 15 rows.

A B C E F
1 100 $100.00 100 $300
2 110 $50.00 130 $200
3 100 $25.00 120
4 $75.00
5 120 $120.00

The entries in col E of tbl2 are unique. What I'd like to do given this data
is:

- Sum tbl2 col F,
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B value to
the sum.
- for each row in tbl1, if item A is found in tbl2 but F is "" then add B
value.

Given the data shown the sum would become:
sum = (F1 + F2) + B2 + B4 + B5 = $300.00 + $200.00 + $50.00 + $75.00 + $120.00

Thanks a bunch,
John

If I follow,

=SUM(F1:F5)+SUMPRODUCT((B1:B5)*(COUNTIF(A1:A5,E1:E 5)=0))

Regards,
Peter T

"DocBrown" wrote in message
...
I'm pretty sure this can be done with sumproduct, but I can't quite get my
head around it.

I have two tables, Tbl1 A & B, Tbl2 E & F. Both are limited to 15 rows.

A B C E F
1 100 $100.00 100 $300
2 110 $50.00 130 $200
3 100 $25.00 120
4 $75.00
5 120 $120.00

The entries in col E of tbl2 are unique. What I'd like to do given this
data
is:

- Sum tbl2 col F,
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B value
to
the sum.
- for each row in tbl1, if item A is found in tbl2 but F is "" then add B
value.

Given the data shown the sum would become:
sum = (F1 + F2) + B2 + B4 + B5 = $300.00 + $200.00 + $50.00 + $75.00 +
$120.00

Thanks a bunch,
John

DocBrown;487185 Wrote:
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B
value to the sum.
This formula might do this bit - but CHECK - I haven't thoroughly
checked it.

=SUMPRODUCT(--(NOT(COUNTIF($E$1:$E$15,$A$1:$A$15)0)),$B$1:$B$15 )

DocBrown;487185 Wrote:
- for each row in tbl1, if item A is found in tbl2 but F is "" then add
B value. Confirm that if there is more than one item A in tbl1 you want to add
all their values. This would arise if instead of $300 for item 100 in
tbl2 you had "". There are 2 values for item 100 in tbl1 ($100 and $25);
add them both?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134340

Hi,

Thanks to both Peter T and p45cal! The COUNTIF function is the ticket I was
looking for. I need to do a little bit of a tweek to get it to fully work
with my requirements, but it's definitely put me on the right track.

Appreciated,
John

"p45cal" wrote:


DocBrown;487185 Wrote:
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B
value to the sum.
This formula might do this bit - but CHECK - I haven't thoroughly
checked it.

=SUMPRODUCT(--(NOT(COUNTIF($E$1:$E$15,$A$1:$A$15)0)),$B$1:$B$15 )

DocBrown;487185 Wrote:
- for each row in tbl1, if item A is found in tbl2 but F is "" then add
B value. Confirm that if there is more than one item A in tbl1 you want to add
all their values. This would arise if instead of $300 for item 100 in
tbl2 you had "". There are 2 values for item 100 in tbl1 ($100 and $25);
add them both?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134340

I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134340

Thanks p45cal,

I'll drop over there and take a look. If I post, I'll follow your suggestion.

The concept you provided gets me 75% there. The solution is still not adding
in the B5 cell. This is the case where the A5 matches the E3, but because F3
is empty, I want to add B5 to the total.

Ideas?

John

"p45cal" wrote:


I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134340

You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your sample
data.

Regards,
Peter T


"DocBrown" wrote in message
...
Thanks p45cal,

I'll drop over there and take a look. If I post, I'll follow your
suggestion.

The concept you provided gets me 75% there. The solution is still not
adding
in the B5 cell. This is the case where the A5 matches the E3, but because
F3
is empty, I want to add B5 to the total.

Ideas?

John

"p45cal" wrote:


I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=134340