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 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

--
ren_juanj
-----------------------------------------------------------------------
ren_juanjo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3715
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 2:50:54 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
396 Views

Similar Articles

[PageSpeed] 50

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

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 3:11:56 PM
You can use SUMPRODUCT but when you have only 1 criterion SUMIF i
better

based on your example

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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 3:13:52 PM
Thanks Daddy.

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

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 3:16:19 PM
Thanks Daddy.

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

VBA Noo

--
VBA Noo
-----------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 3:16:19 PM
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

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=56875

0
8/6/2006 3:45:01 PM
Reply:

Similar Artilces:

sending mail using an alias email address
We are using Exchange Server 2007 with Outlook 2007 clients. I have several email addresses listed under my mail account on the Exchange Server for which I "receive" email. However, the server will not allow me to "send" email using any of these email addresses - as it only allows me to send using the primary address for my email account. I get a message saying "You are not allowed to send this message ... on behalf of another sender without permission to do so." What settings do I need to change on the Exchange Server so that it allows Outlook 2007 ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Copying data from one chart to another
I have many graphs - all plotting on similar scales but using different data. Is there any way I can simply copy one set of data from one graph and paste it into another graph so that I can avoind going through all the hassle plotting each curve again? I want to have graphs showing different combinations of the same data and have hundreds of curves to plot so this could be a huge timesaver... Cheers. -- Alan_Partridge ------------------------------------------------------------------------ Alan_Partridge's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29295 V...

Text to columns
Once I use the Text to columns feature in Excel, it seems there is no way to turn it off. Anyone know if there is a way to reset this so that newly pasted text will not continue to get broken up (for example by the space delimiter) Presently the only way is to exit Excel and restart Excel - then pasted text all goes into one cell regardless of spaces. Hope I explained that well enough Al I may have been to hasty in making this assumption, it appears that the problem I described below is only happening on one workstation - this may indicate that the Excel Registry keys are in need of...

Can I abbreviate one value in a data series?
I've got a chart where one value (8,300) greatly exceeds all the others. Is there a way to abbreviate this value so the other data points show better in the graph? Hi, One way is to break the Y axis, have a look at these examples of how to http://peltiertech.com/Excel/Charts/BrokenYAxis.html http://tushar-mehta.com/excel/newsgroups/broken_y_axis/tutorial/index.html http://www.andypope.info/charts/brokencolumn.htm Cheers Andy CMEknit wrote: > I've got a chart where one value (8,300) greatly exceeds all the others. Is > there a way to abbreviate this value so the other da...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

using parameters
I have a form which the user selects the BlockNo. The other information that is entered in the form is : 1) NoOfRecordedTrees - RT 2)NoOfSurroundingTrees - ST When the BlockNo is entered, a query runs which picks up fertiliser rates for this Block for all sectors within that block. With RT and ST - it should do a calculation such that it uses Rate/ sector * (RT+ST) to find how much fertiliser is needed for each sector in each block. I would like to add a column to the existing query showing FertiliserAmt using these parameters. How do I specify them within the query. Thanks for your great...

Re: Using alias address
Brian Tillman wrote: > Vince <vinresp*@swhome.com> wrote: > > >>We use an exchange server at work for e-mail. I have set up an alias >>that I use for receiving mail but I wanted to use the alias for >>sending all the mail as well. I was told that it cannot be done in >>exchange but I can use a POP server. > > > I don't believe that's always true. For example, I have several accounts in > Outlook where I work, all within the same profile. Only one of those > accounts, the Exchange account, uses my work address. The ot...

how can i set up my pop and smtp account using proxy server in ou.
"Abhishek" <Abhishek@discussions.microsoft.com> wrote in message news:90EE645B-97D9-4145-A368-E26743C547F9@microsoft.com... You'll need instructions from your ISP. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/computers/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! ...

Can Outlook 2003 use MSN Messenger INSTEAD of Windows Messenger?
I didn't get an answer to this question last week so I'm re-posting... I recently purchased a new computer (Windows XP Home Edition w/SP2) and loaded up all of the available updates to the OS, Internet Explorer, etc. Next, I installed Office 2003 Professional. I disabled Messenger integration with Outlook 2003 as discussed in other posts here. Next I installed MSN Messenger 6.2 and it seemed to run properly as a stand-alone application. So I re-enabled Messenger integration on Outlook 2003. The next time I booted up and ran Outlook, the Messenger icon appeared in the taskbar...

Office 207 One Note
My One Note has been working fine for at least 2 years. Then a couple of days ago two pages of my most important information in my main workbook just disappeared. I'm not sure but none of the other pages look the same. Does anyone have any idea what might have happened? Have had no other computer problems recently. -- Rockymtnwoman -------- Original-Nachricht -------- > My One Note has been working fine for at least 2 years. Then a couple of days > ago two pages of my most important information in my main workbook just > disappeared. > I'm not sure b...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

Sumproduct usage?
My brain's on overload at the moment, so I'm turning to you guys for help. I need help w/ a formula that will count the number of sales that someone had based on a certain date...I thought I could use sumproduct, but I'm beginning to wonder if I was wrong about that. The relavant columns are D & M. Col D Col M 12/24/2009 Andy 12/24/2009 Charles 12/24/2009 Andy 12/25/2009 Charles You had the right function in mind. Try something like this... Use cells to hold the criteria: A1 = some date B1 = some name =SUMP...

Creating Contacts For Accounts Using...
Hopefully as Microsoft CRM matures, many small time saving features will be added. One that should be a priority is the ability to add a new contact for an existing account using the common account information, i.e. address, phone number, web address, etc. Retyping the same company address in each time is not very productive. Thank you, Ed Podowski ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Outlook first use
Hi, when I open Outlook in a workstation for the first time, Outlook open setup and ask user to create a new profile. I have a utility to create profile automaticaly and this setup is deleting existing profile. Is it possible to Outlook don�t ask to create a new profile for the first time? Tks. Alex ...

Scroll horizontaly with mouse, create same system used to scroll .
Hi, I think it would be great if mouses adopted a second scroll button, for horizontal scrolling, just like the vertical one .... Indeed, when you work with wide Excel spreadsheets, you can easily scroll down but to scroll from left to right or vice-versa, you have to use the scroll bar or arrows and it's annoying... So, am I a millionnaire yet??? Hi Frederic, > I think it would be great if mouses adopted a second scroll button, for > horizontal scrolling, just like the vertical one .... Indeed, when you work > with wide Excel spreadsheets, you can easily scroll down b...

Calculating Correlation using arrays
I have a sheet full of data for many products in chronological order. Column A is Date of manufacture Column B is time of manufacture Column C is the product Column D is a measurement from the automatic control system Column E contains occasional manual measurements for calibratio checks Up till now I have split the data out by grade and checked calibratio using pivot tables and also checked slope and offsets. After gainin exposure to all kinds of clever functions via this board I now wonde whether it would not be possible to automate these checks in some way ie on a separate sheet I list t...

can I use 11 x 14 paper in office documents?
Is it possible to use a larger sheet of paper when using publisher? Yes. First select the paper size in the Printer Setup. -- Don Vancouver, USA "Prairie Inn" <Prairie Inn@discussions.microsoft.com> wrote in message news:FC032907-D965-4DE9-891D-40DF9C6E9C8C@microsoft.com... > Is it possible to use a larger sheet of paper when using publisher? If your printer can handle it, yes. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "Prairie Inn" <Prairie Inn@discussions.microsoft.com> w...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Online Restore using NT Backup has no edb.chk or edb.log files
I have a single site with four servers running Exchange 5.5 SP4 on NT4 SP6a. I am using an internal 35/70 Compaq DLT. When I back up two servers at the same time using online method, I am missing the edb.log and and edb.chk files when trying to restore the db's. Is there a known issue for this? Thanks, Jim When you make online backup, you are backing up the database content perse, the logs files will be skipped because ntbackup cannot back up open files. I recomend you to adquire a third party backup software with open files and exchange database options, like Veritas to ensu...

Bulk sending of emails in Outlook being rejected if one address is wrong.
I am sending bulk emails in BCC and they are all being returned to me because one or more of the email addresses is incorrect. Is there a way to setup Outlook so that the emails go to the correct addresses and the incorrect addresses are bounced back to me? I use the Mail Merge feature and send out a newsletter with over 1000 e-mail addresses (who signed up for the newsletter so it is not spam). My service provider, Comcast, just put a limit of 10 at a time. It's real fun sending these out monthly!! >-----Original Message----- >I am sending bulk emails in BCC and they a...

use excel to create PP slides?
I'm not sure if this is possible but I have a list of information in an excel table and I'd like to auto matically generate a slide for each row of data. for instance, I have a list of products and each product is on it's own row with 6 product features in the following columns. How can I take that info from excel and create a features slide for each product using the information from the following columns. I don't mind cutting and pasting the list - it doesn't have to be fully automatic but as I have over 1000 products I desperately don't want to reype ...

Using BGInfo Command Line Parameters
I've read the Help file in BGInfo - but can't figure out how to use Command Line Parameters. When I click on my startup icon, I would like to invoke certain parameters. The line I came up with is: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe /timer:99 /popup /taskbar /rtf:C:\DATA\BGINFO /nolicprompt" Obviously, this is wrong. My Target line reads: "C:\Program Files\BGInfo\BgInfo\Bginfo.exe" My Start in line reads: "C:\Program Files\BGInfo\BgInfo" Please help me by telling me what, exactly, should go in each line. I would also like t...