sumproduct .. validating 2 criterias (multiple tabs)

hi guys,

i am trying to add additional functionality to my Project Budget
Expendature worksheet of which some of you may have seen.

anyways, the idea is to have sumproduct calculate all items that have
the same GL code & date of which it was purchased (date of purchase)

currently i have the following
TAB: Spend Calendar
ROW C8:N8 = Month #'s (Jan, feb ect)
COL B13:B36 = GL Codes (130000,190000 ect)

TAB: 001-013 (13 tabs)
CELL C6 = Date of Purchase
COL C24:C37 = GL Codes of each item within the purchase
COL J24:J37 = Total cost of the item which the GL Code is related to

can someone advise as to what the sumproduct formula would be for it
to validate the GL Code AND month of the purchase to provide the total
spend of that month?

someone here had provided this formula (to validate something else,
but looks relevant some how)
=SUMPRODUCT((SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$C
$24:$C$37"),$B56,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$J
$24:$J$37")))
but not sure how to manipulate this to achieve what i am looking for

cheers

0
11/8/2007 6:04:39 AM
excel 39879 articles. 2 followers. Follow

3 Replies
900 Views

Similar Articles

[PageSpeed] 52

Chuck,

My standard advice in this type of situation is to bite the bullet, and combine all the sheets into 
one sheet, with an additional column that contains the original sheet name.  Using multiple tabs for 
the same type of data is simply bad design - use one database (on one tab), and then filter or Pivot 
the data to get the view that you want.

There are many examples of macros that you can use to combine your data tables.  Below is one, with 
the assumption that the data tables start in cell A1 of each sheet and are contiguous - no entirely 
blank rows or columns. Also, the heading should be in row 1, the same for every sheet.

HTH,
Bernie
MS Excel MVP

Sub ConsolidateSheetsIntoDataBase()
With Application
   .DisplayAlerts = False
   .EnableEvents = False
   .ScreenUpdating = False
End With

Dim i As Integer
Dim myDB As Worksheet

Set myDB = Worksheets.Add(Before:=Worksheets(1))
myDB.Name = "DataBase Sheet"

Worksheets(2).Cells.Copy Worksheets(1).Cells

With Worksheets(1)
   .Cells(1, 1).EntireColumn.Insert
   .Cells(1, 1).Value = "Department"
   Intersect(.Range("A2:A" & .Rows.Count), _
             .UsedRange).Value = Worksheets(2).Name
End With

For i = 3 To Worksheets.Count
   With Worksheets(1)
      myRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      Worksheets(i).Cells(1, 1).CurrentRegion.Offset(1) _
            .Copy Worksheets(1).Cells(myRow, 2)
      Intersect(.Range("A" & myRow & ":A" & .Rows.Count), _
                .UsedRange).Value = Worksheets(i).Name
   End With
Next i

With Application
   .DisplayAlerts = True
   .EnableEvents = True
   .ScreenUpdating = True
End With

End Sub



"Chuck" <cvanoosbree@gmail.com> wrote in message 
news:1194491868.070738.314010@v29g2000prd.googlegroups.com...
> hi guys,
>
> i am trying to add additional functionality to my Project Budget
> Expendature worksheet of which some of you may have seen.
>
> anyways, the idea is to have sumproduct calculate all items that have
> the same GL code & date of which it was purchased (date of purchase)
>
> currently i have the following
> TAB: Spend Calendar
> ROW C8:N8 = Month #'s (Jan, feb ect)
> COL B13:B36 = GL Codes (130000,190000 ect)
>
> TAB: 001-013 (13 tabs)
> CELL C6 = Date of Purchase
> COL C24:C37 = GL Codes of each item within the purchase
> COL J24:J37 = Total cost of the item which the GL Code is related to
>
> can someone advise as to what the sumproduct formula would be for it
> to validate the GL Code AND month of the purchase to provide the total
> spend of that month?
>
> someone here had provided this formula (to validate something else,
> but looks relevant some how)
> =SUMPRODUCT((SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$C
> $24:$C$37"),$B56,INDIRECT("'"&TEXT(ROW(INDIRECT("1:13")),"000")&"'!$J
> $24:$J$37")))
> but not sure how to manipulate this to achieve what i am looking for
>
> cheers
> 


0
Bernie
11/8/2007 3:17:26 PM
hi Bernie

thanks for your comments. unfortunately i have to use multiple tabs
for this particular sheet as each purchase is concidered seperate. now
just trying to create a consolidated view of those purchases of each
code by each month. this was never requested before, but found out it
was a big thing for the PM's i work with (to include myself) to do
some type of reporting (as our finance system lacks any type of
accurate reporting, heck reporting in general)

also, the tabs must be maintained as each time i / other PM's make a
purchase, the tab is sent to the finance department to propigate based
on the details of those tabs

ill try and post this on the other NG to see other ideas

cheers
Chuck

0
11/8/2007 10:00:35 PM
Chuck,

> also, the tabs must be maintained as each time i / other PM's make a
> purchase, the tab is sent to the finance department to propigate based
> on the details of those tabs

Create one tab, with a cell for a key number, and formulas that extract data from your database 
based on the key number. Put all your headers, text, comments, and data fields wherever you want 
them.

So, let's say you have your data base on a sheet DataBase, and it looks like this:

Key         Data1    Data2    Data3
Key1     Value1-1    Value2-1    Value3-1
Key2     Value1-2    Value2-2    Value3-2

On your report sheet, use VLOOKUP formulas wherever you want data, like

=VLOOKUP($A$1,DataBase!$A$1:$D$10000,2,False)
=VLOOKUP($A$1,DataBase!$A$1:$D$10000,3,False)
=VLOOKUP($A$1,DataBase!$A$1:$D$10000,4,False)

Enter Key1 into cell A1 to return Value1-1 , Value2-1, and Value3-1 with the above formulas.

Then, to send the data to the finance department, enter your key value into cell A1, copy that 
sheet, convert to values, and you're done.

You will have your database, and the ability to create a report based on any set of data in the 
database.

HTH,
Bernie
MS Excel MVP


"Chuck" <cvanoosbree@gmail.com> wrote in message 
news:1194559235.856418.79800@y27g2000pre.googlegroups.com...
> hi Bernie
>
> thanks for your comments. unfortunately i have to use multiple tabs
> for this particular sheet as each purchase is concidered seperate. now
> just trying to create a consolidated view of those purchases of each
> code by each month. this was never requested before, but found out it
> was a big thing for the PM's i work with (to include myself) to do
> some type of reporting (as our finance system lacks any type of
> accurate reporting, heck reporting in general)
>
>
> ill try and post this on the other NG to see other ideas
>
> cheers
> Chuck
> 


0
Bernie
11/9/2007 2:05:19 PM
Reply:

Similar Artilces:

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Difference between 2 Opportunities reports
Can you help me to understand the difference between 2 Opportunity reports: 1. Pipeline Chart report by sales stages 2. Pipeline Chart report forecast by sales stages ...

reporting tools #2
What reporting tools do you all like? I have looked at a couple but would like to know what you all recommend? I'd recommend Quest MessageStats. http://www.quest.com/messagestats/ Nue "Brandon" <whocares@you.com> wrote in message news:%23Y%231IlbeGHA.1436@TK2MSFTNGP05.phx.gbl... > What reporting tools do you all like? I have looked at a couple but would > like to know what you all recommend? > > ...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

Excel Chart #2
I am developing a chart on Excel. I have two columns with numbers. A third column has a formula to average the two numbers. I want to graph the average. Problem is my graph looks bad. If I enter the two numbers, they are averaged in the third column and graphed correctly. The problem is Excel interprets rows that have not been entered as zero and graphs them as zero. I would like rows that have not yet have data entered into them to be treated as if there is no data and thus not be graphed. Hope someone can help me? I need this for a project I working on to graph the pH of ...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

Date Subtraction #2
Hi I run Win2K with Excel 2K. I would like to enter a date in a cell (eg Oct-05) and have the preceding 11 cells automatically put the previous months in. Example: In cell A15 = Oct-05 (entered) In cell A14 = Sep-05 (automatically) In cell A13 = Aug-05 (automatically) In cell A12 = Jul-05 (automatically) etc etc etc Is there a formula that can do this? Any help will be much appreciated...thanks ! John On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder" <JohnCalder@discussions.microsoft.com> wrote: >Hi > >I run Win2K with Excel 2K. > >I would like to ent...

MX Records #2 #2
I would like to setup an MX record #2 of weigh 20 for another location - not on same domain or even same network. The goal is to hold mail in case that primary MX record / server is down. How do I setup the SECOND server for this setup? Thank you. On Fri, 21 Apr 2006 12:41:55 -0500, ">>Smith<<" <jjsmith@msn.com> wrote: >I would like to setup an MX record #2 of weigh 20 for another location - not >on same domain or even same network. The goal is to hold mail in case that >primary MX record / server is down. How do I setup the SECOND server for...

Using 2 versions of Mac Office 2004 (Japanese and English). How do I set the default for which version my machine uses to open office files?
I using 2 versions of Mac Office 2004 (Japanese and English). How do I set the default for which version my machine uses to open office files? It would of course be better if MS allowed users to switch menu languages within the program just as Mac does with its OS and associated software. But since that is not the case, I have had to purchase install the English version of Office 2004 (at great expense both monetarily and memory wise). When I open files already on my computer it always opens them with the "default" version of Office 2004 which is in Japanese. I can't seem to ...

Fixed Allocation #2
We have about 40 departments. Currently, we allocate several expense items to these departments based on pre defined percentages. For example: 7130 Telephone is allocated to 40 Departments 7140 Utilities are allocated to 40 departments 7150 Office supplies are allocated to 40 departments. We have about 12 expense accounts that are allocated to the 40 accounts. The percentage table is same for all accounts and is updated once a year. My question is Becauase I have 12 expense accounts that allocated to 40 departments would I need to set up 12 fixed a...

All Day events cover 2 day
When I create an all day event or even an event that covers several days, it looks fine in monthly view if you look at it in a list view it spans over to two days. So when you sync your calendar your pda shows the event as 2 days. The default for an all day event is 12:00 am to 12:00 am is there a style sheet that can be changed to reflect an all day event to be 23 hours and 59 mins. Add the duration field to the view and you'll see they are only 1 day. If they spread over 2 days on the pda, check the time zone settings on the pda. -- Diane Poremsky [MVP - Outlook] Out...

Multiple IF Condition
I posted the following in worksheetfunctions.. but I understand this NG is not active so posting it here again. Hello All, I am using Windows XP/Office 2003 and have the following problem: I have a worksheet with 8 Columns as follows: A B C D E F G H S# Ref. No Date CODE Company Problem Status ClosedDate I wish to test two Cells viz. Column C (Date) and Column H (ClosedDate). If the date entered in Date is greater than or equal to Today(), the value displ...

How do I draw multiple arrowheads on the same line?
I am creating a flow chart with many boxes connecting into one. Because the connectors merge together before reaching the destination box, my co-workers want to see multiple arrowheads along the length of each connector so they can tell the direction of the flow is toward the box and not back up one of the other connectors. AFAIK the flowchart routing style will always cause dynamically glued connectors to go to the centre of the line (of the bounding box). If you use static glue then you can connect to connection points. Therefore, you add more connection points to your flow chart shap...

Exchange Calendar #2
If we schedule a meeting, no one can see anything on anyones calendar past 3/31/2004. Is there a setting to change this? ...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

Need recommendation for a high-speed barcode label printer suggestion #2
Currently we mainly use Zebra 2824 printers and they work great with RMS. We now have the need for a higher speed printer (2824 not cutting for the volume) - one that is a single label feed (1.25 x 1 is fine) AND the ability to autocut between each sku that's sent to the printer from RMS (this may not be possible with RMS...I do not know...part of what I'm trying to find out). Any suggestions or someone out there doing something similar? Thanks! Chris ...

Need help with data validation format
I'm trying to make this format mandatory when someone enters his/her data in a cell. The format would be this : "### ###". Therefor, if the worksheet user doesn't enter a chain like this one : "113 244", the data just won't enter. And yes, the space beetween the 3 first and last numbers has to be there. I can't seem to find anything in the Data / Validation menu that enables me to do this :confused:. Any help would be much appreciated. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

Hiding multiple addresses in an e-mail
I want to be able to send an e-mail to multiple addresses, or a group address that I have set up. My question is this: How can I do this so each individual that receives the e-mail will only see their e- mail address, or the e-mail Group name? Please help! Thanks, Jaydoto@cox.net There are two ways you can approach this. You could create a new contact with a name like "Hidden Recipient List" and assign your e-mail address to it, then put that contact in the To field and all the other addresses in the BCC field. When the recipient gets the message, he/she will see only "...

Offline store & multiple profiles
Hey there, a customer of us has some employees that work at multiple locations. These locations all have their own SBS server, making trusts between them impossible. Because of this they multiboot, several employees can boot their laptop in 2 installs, one for location X, the other for location Y. On both locations they need outlook configured to access the location X exchange server. This isn't such a problem, but the offline store replication is, as some mailboxes are insanely large. Is there a possibility (or a better solution) in which we can have 2 user profiles, but only one outlo...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

"To" Field #2
is it possible to change the contents of the "to" field of the message in my outlook mail box? Any insight would be highly appreciated Many thanks in advance Can you try to explain a bit further? Which Mailbox do you mean? Inbox, sent items etc. Or do you mean when making a new email? In what situation do you want to change the contents of To... field? Judy Gleeson - MVP Outlook Acorn Training and Consulting Canberra, Australia see what Outlook training can do to improve productivity: www.acorntraining.com.au/pdfdocs/ProductivITwithOutlook.pps www.acorntraining.com.au/...

Reprot builder 2.0 how to use URL access parameter
Hi, i will create a link button in my system, when the user click the link, it will redirect to reporting services to open a report, for example the link is http://test-server/Reports/Pages/Report.aspx?ItemPath=%2fCustomer+Sales+Listing&CompanyID=DEMO which CompanyID is my parameter i want pass to the report. When the report load how i can receive this parameter and filter the data column CompanyID=DEMO only will appear in the report. Thanks Yue ...

Increasing the speed of Sumproduct
Hi, 1. Just read http://www.mcgimpsey.com/excel/formulae/doubleneg.html In this JEM says that we have double negs so that =SUMPRODUCT(--(A1:A5>10),B1:B5)) can be coerced in to 1. As per JEM"s explanation single unary will coerce True/False to Zero/One and the second double unary is used so that the negative values could be converted to its original sign. My "reasoning" was instead of using double negative sign why not use a single + sign and achieve further speed increase. So I did this =SUMPRODUCT(--(A1:A5>5),--(A1:A5<10),B1:B5) . The data I used was ...

validation for decimals
Hi there. I need to set a series of cells so that the user cannot enter anything other than 2 decimal currency values. Here's my code for this: ..Range("K" & sI & ":K" & sI).Validation.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0.00", Formula2:="999999999.99" It works but it still allows the user to enter more than 2 decimal places. There is no setting that I can find to limit the user so he/she cannot enter 3 or 4 or 5 or more decimal places. I know I can foramt the cells t...