probably an easy one but... (using SUMPRODUCT?)
 

Hi guys, looks like a nice forum you have here.

I've started making a record of all the purchases every month that my
kitchen makes. I've set every supplier to an ID code (001,002,003 etc
etc) and i record this code in the same row as each purchase amount. So
for a month, i have a list of purchases, with totals excluding vat, vat
and then a total inclusive of vat. At the end of the month i can simply
sum the last column to see how much i've spent at the end of every
month.

However i'd like to see how much i've spent with each supplier for the
month. This i'm struggling a bit with. I understand that i need to
search for a supplier ID, where it is apparent in the ID column i am to
add the total for that purchase to the running total.

Its very similar to the FAQ problem and answer that reads
__________________________________________________ _________

Problem:
Counting the number of \"red\" items which cost less than $1000.

Solution:
Using the SUMPRODUCT function to count the number of items meeting the
above criteria, as follows:
=SUMPRODUCT((C2:C6<1000)*(B2:B6="red"))


Item______Item\'s Color____Price
Jacket____red_____________$500
Jeans_____blue____________$200
T-Shirt___red_____________$1,200
Shoes_____black___________$800
Socks_____red_____________$700

Result 2

__________________________________________________ ______________

Except here i'd want to be finding the total spent for every item that
is red, (in this case i'd want my result to be $2,400) not the number
of items that meet the given criteria.



Any help is very much appreciated. Thanks!


--
ren_juanjo
------------------------------------------------------------------------
ren_juanjo's Profile: http://www.excelforum.com/member.php...o&userid=37153
View this thread: http://www.excelforum.com/showthread...hreadid=568751

probably an easy one but... (using SUMPRODUCT?)
 

Try something like this

Change range and month to your range and month.

Col A has Dates
Col B has your account No e.g 001
Col C has value before Vat (Change range to Inc Vat range)

=SUMPRODUCT(--($A$19:$A$27=--"01/07/2006"),--($A$19:$A$27<=--"31/07/2006")*(--(B19:B27="001")*(--(C19:C27))))

You can learn more about Sumproduct on the attached link

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568751

probably an easy one but... (using SUMPRODUCT?)
 

You can use SUMPRODUCT but when you have only 1 criterion SUMIF is
better

based on your example

=SUMIF(B2:B6,"red",C2:C6)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568751

probably an easy one but... (using SUMPRODUCT?)
 

Thanks Daddy.

Wasn't sure if this list was for the year so give him Sumproduct to be
on the safe side

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568751

probably an easy one but... (using SUMPRODUCT?)
 

Thanks Daddy.

Wasn't sure if this list was for the year so give him Sumproduct to be
on the safe side

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568751

probably an easy one but... (using SUMPRODUCT?)
 

No problem Noob, I wasn't responding to your post, just answering the
query.

Of course, if there is data for more than the one month then a
SUMPRODUCT formula would be entirely justified to sum based on multiple
criteria including dates.

However, I think your formula has an unnecessary mixture of approaches.
I'd change to

=SUMPRODUCT(--(A19:A27=DATE(2006,7,1)),--(A19:A27<=DATE(2006,7,31)),--(B19:B27="001"),C19:C27)

If your date range is always a calendar month

=SUMPRODUCT(--(TEXT(A19:A27,"mmm yy")="Jul
06"),--(B19:B27="001"),C19:C27)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568751