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

  • Thread starter Thread starter ren_juanjo
  • Start date Start date
R

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 m
kitchen makes. I've set every supplier to an ID code (001,002,003 et
etc) and i record this code in the same row as each purchase amount. S
for a month, i have a list of purchases, with totals excluding vat, va
and then a total inclusive of vat. At the end of the month i can simpl
sum the last column to see how much i've spent at the end of ever
month.

However i'd like to see how much i've spent with each supplier for th
month. This i'm struggling a bit with. I understand that i need t
search for a supplier ID, where it is apparent in the ID column i am t
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 th
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 tha
is red, (in this case i'd want my result to be $2,400) not the numbe
of items that meet the given criteria.



Any help is very much appreciated. Thanks
 
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 Noo
 
You can use SUMPRODUCT but when you have only 1 criterion SUMIF i
better

based on your example

=SUMIF(B2:B6,"red",C2:C6
 
Thanks Daddy.

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

VBA Noo
 
Thanks Daddy.

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

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

Of course, if there is data for more than the one month then
SUMPRODUCT formula would be entirely justified to sum based on multipl
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")="Ju
06"),--(B19:B27="001"),C19:C27
 
Back
Top