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

[ren_juanjo]

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!

[VBA Noob]

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

[daddylonglegs]

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

based on your example

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

[VBA Noob]

Thanks Daddy.

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

VBA Noob

[daddylonglegs]

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)