Excel 2013 - SUMIF and VLOOKUP together 2

[pendle666]

Hello

My SUMIF works fine alone and my VLOOKUP works fine alone but I am unable to combine them successfully....

Main data looks like this (I've left out the row data not relevant to this problem):

[pre]

A B C D E F G
6 Supplier No Supplier Our Ref Your Ref Code CCNo Amount
7
18 12345 ABC Company xxx xxx xxx xxx


[/pre]


Normally we add in the amounts manually but I want to automate if possible.

The source data looks like this: currently it's sitting on the same worksheet....
[pre]
J K L M N
21 Office xxxxx xxxxx Amount xxxxx
22 ABC Company xxxxx xxxxx £4.88 xxxxx
23 ABC Company xxxxx xxxxx £19.91 xxxxx
24 ABC Company xxxxx xxxxx £42.62 xxxxx
25 ABC Company xxxxx xxxxx £16.99 xxxxx
26 XYZ Company xxxxx xxxxx £17.46 xxxxx
[/pre]

In Cell G18 I can have the following formula:
=SUMIF(table,J22,M22:M26) and it gives me the answer £84.40 Table is the name of the source data from J22:N26.
or I can have
= VLOOKUP (B18,table,4,false) gives me the answer of £4.88.

But what I actually want to do is add up all the totals in Table for ABC Company and put the total into G18.

I've had a look online and there are some examples, but I just can't get them to work for me, and I can't see where I'm going wrong. I either get the message that I've got too many arguments or it thinks I'm trying to enter text rather than a formula.

Can someone point me in the right direction please?

Thank you

thank you for helping

____________
Pendle

 

[jonsi]

Hi Pendle ...you're nearly there

If you put (SUMIF($J$22:$J26,$B18,$M$22:$M$26)) you should get what you want

Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[SkipVought]

hi,

Use SUMPRODUCT() in this form, using Named Ranges, where the heading value defined the name of the range...
[tt]
=SUMPRODUCT((Office=B18)*(Amount))
[/tt]

BTW, you can string as many qualifiers together as needed. This is much more intuitive than SUMIFS() or COUNTIFS(), much more flexible than VLOOKUP() such that I rarely use ANY of those in favor of SUMPRODUCT()

 

[pendle666]

Excellent, thank you very much.



thank you for helping

____________
Pendle

 

[pendle666]

Skip:

Using SUMPRODUCT though - isn't that multiplying my figures? This is just a sum which is why I was using SUMIF.


Pendle

thank you for helping

____________
Pendle

 

[SkipVought]

They are both correct, but which is more understandable???
[tt]
=SUMIF($J$22:$J26,$B18,$M$22:$M$26)
[/tt]
or
[tt]
=SUMPRODUCT((Office=B18)*(Amount))
[/tt]

I'd even opt for this...
[tt]
=SUMIF(Office,$B18,Amount)
[/tt]

If you were using Structured Tables, you would not even need to Name the Ranges!!!

 

[SkipVought]

In fact here's the solution using Structured Tables, where the source table is Table1 and the table in rows 6:18 is Table2...

[tt]
=SUMPRODUCT((Table1[Office]=[@Supplier])*(Table1[Amount]))
[/tt]

This is quite self documenting!

 

[pendle666]

THank you - I shall try the different options so I can understand them better. I did try using structured tables, but I wasn't doing it right (I didn't realise that at the time) so I didn't understand why they weren't working.



thank you for helping

____________
Pendle

 

[SkipVought]

If you wish, post a separate thread addressing your questions about structure tables.