Re: Sumproduct forumla for complex sum.

Hi guys, I appreciate your help on this.

Hi p45cal,

Your solution is working except that it doesn't pick up the $120 value in
B5. In that case, the value 120 in A5 is found in col E, but since there's no
entry in F3, I need that value to be added also.

I'm thinking of a MATCH or SUMIF or something like that. Any ideas?
Here's NEW data that has all the conditions, I think.

A B C D E F
1 100 $100.00 100 $300.00
2 120 $121.00 120
3 $75.00 130 $200.00
4 100 $25.00 140 $250.00
5 140 $218.00

The total should be:
Sum(F1:F5)+ ( B3 ) + ( B2 ) = 750.00 + 75.00 + 121.00 = 946.00
B2 is added because no value is in F2,
B3 is added because no id is in A3.

Note that all values present in A are found in E. Extra values may be
present in E.

John

"Peter T" wrote:


"p45cal" <p45cal.3yk4n3@xxxxxxxxxxxxxxx> wrote in message
news:p45cal.3yk4n3@xxxxxxxxxxxxxxxxxx

Peter T;488737 Wrote:
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 TYes, Peter, it gives the same result for his example. But further
testing yields some odd results; If the ranges in your formula are
expanded to cater for the full 15 rows possible, then when you:
Add a new item in table 1 after the last entry, call it 101 and give it
the value $3 in the second column of table 1,
then add 101 after the last entry in column 1 of table 2 and leave the
value empty..

I -*think *-the answer should be 748, but your formula gives 673.


Indeed you are right, I see why it's wrong but trickier to fix than I
thought!
Of course would be much easier if the data is tidied up a little beforehand.

Regards,
Peter T




.

Relevant Pages

  • Re: start up message
    ... reminder.exe Entry point not found the procedure ... Hi, Peter. ... tab for the reminder.exe and uncheck it. ... any other unnecessary programs. ...
    (microsoft.public.windowsxp.general)
  • Re: firefox unuseable?
    ... mostly on text entry it seems. ... functional Firefox, and I can't thank you enough. ... Will it affect other apps which use UnixHome? ... Does Peter N know about this? ...
    (comp.sys.acorn.apps)
  • Re: wikipedia mistakes (was Re: Whats the difference)
    ... > in article 40FD3BF4.41FA@worldnet.att.net, Peter T. Daniels at ... >> syllabary is a dead end. ... > deemed the entry 'vanity' and started a big movement to delete it. ...
    (sci.lang)
  • Re: Help with a Form function please
    ... What is the worksheet data like, and where in the form do you want to put ... "Peter" wrote in message ... > Each entry is contained in a row of about fifteen columns - what I ... > Remove the INVALID to reply ...
    (microsoft.public.excel.programming)
  • Re: Sumproduct forumla for complex sum.
    ... Peter TYes, Peter, it gives the same result for his example. ... Add a new item in table 1 after the last entry, call it 101 and give it ... the value $3 in the second column of table 1, ...
    (microsoft.public.excel.programming)
Loading