Sumproduct Understanding Cell Value

Mar 26, 2014

I have this formula -

=SUMPRODUCT(('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$E:$E=B4)*('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$D:$D=$A$2))

Which works perfectly if A2 has the cell value 24/03/2014 however, if i put in $A$2 this value

="02"&"/"&$XFC$1&"/"&"14", where XFC$1 equals =IF(XFD1>9,XFD1,"0"&XFD1) and XFD1 = =MONTH(TODAY())

Which in basic term gives the month value "03"

The cell shows the value 24/03/2014 however does not work with the sumproduct.. is there anyway to get sumproduct to read this cell value as 24/03/2014 as the whole point is so every month I don't have to update anything and it will constantly update...

The only alternative i can think of is quickly making a VBA script to hardcode the cell values for the dates and change the month value for the middle of the cells every month.

View 3 Replies


ADVERTISEMENT

Not Understanding VLOOKUP

Jun 30, 2008

I have searched for a while on VLOOKUP (something I'm not very familiar with but and trying to become more so), And if this wasnt such a time sensitive project I would continue to search. In one sheet, there is a column which includes employee #'s, the next column over is the employee names, next is salary. On another sheet I put a drop down box where you can select the employee number. In the cell next to that I would like to have the employees name come up as well as the salary in the next cell over....something like this

Name Salary
Dropdown w/#'s

Taken from


Emp# EmpName EmpSalary
23123 Joe Smith $50,000

Is this the right time for vlookup...there are almost 1000 names in this sheet?...

View 9 Replies View Related

Understanding Template

Dec 8, 2008

I have a workbook with several worksheets and numerous userforms, commandbuttons and macro's. I want to prevent people from saving over the workbook once they have used it, but allow save as using a different workbook name.

I tried saving the book as .xlt, but then when I reopen it and make material changes, it lets me save over it. I have't worked with templated before, is there something I am doing wrong?

View 9 Replies View Related

Understanding How FindFirst Works

Mar 5, 2008

How do I use Find to locate the first occurence of a particular value in a cell, and return the row that the cell is in?

View 14 Replies View Related

Understanding Automated Email Code

Dec 19, 2006

it has been designed to send out automated emails using Excel and Lotus Notes, unfortunately the code was give to me to modify and the person who created it didn't comment the code at all so i'm finding it impossible to see what each part is doing: ....

View 14 Replies View Related

Understanding Code: Extend The Analysis

May 2, 2006

i need to extend the analysis but i have no idea what the 2 modules below do.

Sub mmm()
zonecode = Worksheets("sheet1"). Range("a65536").End(xlUp).Row
etypes = Worksheets("sheet1").Range("iv1").End(xlToLeft).Column
nextline = 2
For i = 2 To zonecodes
zcode = Worksheets("sheet1").Cells(i, 1).Value
For j = 2 To etypes
etype = Worksheets("sheet1").Cells(1, j).Value
enbr = Worksheets("sheet1").Cells(i, j).Value
Worksheets("sheet2").Cells(nextline, 1).Value = zcode
Worksheets("sheet2").Cells(nextline, 2).Value = etype
Worksheets("sheet2").Cells(nextline, 3).Value = enbr
nextline = nextline + 1
Next j
Next i
End Sub....................

View 2 Replies View Related

Public And Private Sub: Move To The Next Curios Level Of Understanding Macro And VB Coding

Jun 29, 2006

Where can I obtain some working examples or explanations as to what, where, when, why, and how these Public and Private Sub are correctly implemented? I see these code used extensively but not sure why it's there yet. A macro which uses a Private Sub is in one of my workbooks. Due the "progress" of things from here I now need to move to the next curios level of understanding Macro and VB coding.

View 3 Replies View Related

Understanding Code (code In Eglish Specially After The .Count /14-1)

Feb 6, 2009

Can someone explian to me this line of code in eglish specially after the .Count /14-1

View 2 Replies View Related

Last Cell Of A Row In SumProduct

Apr 2, 2009

How do i change the formula, =SUMPRODUCT(c14:au14,c15:au15) to include more columns added to row 14 and 15 in future.

Since the column count will increase , the sumproduct has to be like
=SUMPRODUCT(c14:"last cell in 14th row", c15:"last cell in 15th row")

View 9 Replies View Related

Sumproduct On Cell Reference?

Mar 23, 2012

I have formula but would like to change the ="03-01-2011" to reference what is in a cell =a2, etc.

=SUMPRODUCT(--(LEFT($B$2:$B$3582,10)="03-01-2011"))

View 8 Replies View Related

Add SUMPRODUCT To Cell With Macro

Aug 14, 2007

I am trying to use the sumproduct function with ranges defined in my VBA code. I can’t seem to get it to work.

I have two spreadsheets in the enclosed work book – T1, T2

T2 has four columns with the first three columns are text and the fourth column is an integer. The rows in this sheet may be repeated.

T1 has four columns similar to the first three columns in T2. However, the rows are unique. I am trying to do a sumproduct for obtaining the counts on the fourth column in sheet T1

To obtain the values for the fourth column (for each row) in T1 I do the following ...

View 4 Replies View Related

Using SUMPRODUCT To Count Certain Letters In A Cell?

Jun 4, 2014

I am trying to use SUMPRODUCT on one of my excel files. the condition is that it will try to count all those cells that contain certain letters. i tried using this formula, =SUMPRODUCT(--('All Computers'!$A$2:$A$100000="USA*"),--('All Computers'!$E$2:$E$100000="June")), but I am getting a 0 value.

View 3 Replies View Related

SUMPRODUCT Criteria Via Cell Reference?

Apr 12, 2006

SUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).

Is there a syntax that SUMPRODUCT will accept that does the same thing? Note the inclusion of a comparison operator is important.

View 9 Replies View Related

Sumproduct Cell Reference Not Working

Jun 18, 2009

I have the following line of code in the code for a textbox in a userform:

View 4 Replies View Related

How To Not Include Particular Cell In SUMPRODUCT Formula

Aug 28, 2012

With the formula I have as:

=SUMPRODUCT(--(GLCodes=$B7),MasterNetAmt_01)
I want to dis-include the CC="06" in the summation. I tried using:
=SUMPRODUCT(--(GLCodes=$B7),(cc="06"),MasterNetAmt_01)

But it gives a wrong calculation

I use "--" because i have character in the cell

View 1 Replies View Related

SUMPRODUCT With Cell Reference Rather Than Text

Sep 2, 2008

{=SUM(IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUM(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2: F$188))+SUM(IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUM(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207 '!F$2:F$188))+SUM(IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUM(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6) ,'AS21'!F$2:F$188))}

Failed to convert using

=SUMPRODUCT(--((IF(MONTH('3107'!$A$2:$A$188)=MONTH($A6),'3107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('3207'!$A$2:$A$188)=MONTH($A6),'3207'!F$2:F$188))+SUMPRODUCT(--IF(MONTH('4107'!$A$2:$A$188)=MONTH($A6),'4107'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('4207'!$A$2:$A$188)=MONTH($A6),'4207'!F$2:F$188))+SUMPRODUCT(--((IF(MONTH('4307'!$A$2:$A$188)=MONTH($A6),'4307'!F$2:F$188))+SUMPRODUCT(--(IF(MONTH('AS21'!$A$2:$A$188)=MONTH($A6),'AS21'!F$2:F$188)))))))))

A6 is a month column and looks like this "Jan-08"
the '3107', '3207', '4107', etc. are the names of specific sheets, so I'm summing over multiple sheets.

View 9 Replies View Related

Counting A Number In A Cell When Sumproduct

Apr 4, 2007

I have a spreadsheet with 3 coloumns. In A I enter Address, B Date, C Ward/District Area (this is a data validation list). At present I use; = SUMPRODUCT((HFRA!B4:B2000>=Summary!B1)*(HFRA!B4:B2000<=Summary!B2)). Summary B1 and B2 are dates to search between e.g 1/01/07 and 31/01/07. The above formula only counts the date enteries between B1 and B2.

I would like to change this so that it still checks that the date still falls between B1 and B2 but is adds together the numbers entered in coloum A. So coloumn A would become number of completed actions not an address.

View 2 Replies View Related

Sumproduct By Reference Cell As Criteria

Jun 5, 2007

In reference to an earlier thread, which shg and ByTheCringe2 both were genereous in their assistance, I would like to add a wrinkle, as requested by the end-users.
how to reference an earlier thread for people to reference but you can search " Sumproduct By Last (criteria) Month With Data". Edit by ByTheCringe2: Sumproduct By Last (criteria) Month With Data.

We had used the MAX function to find the most recent investments, per counselor, per bank. I now need to find the most recent investments as of a certain date (month-end perhaps). I have ideas, but I'm afraid my misperceptions/assumptions might hinder me from seeing all available options. Initially, I thought I could use a text or listbox on the summary sheet to enter a date that would link to the reference cell on the data page, and this reference cell would then be used in whichever formula I came up with.

View 4 Replies View Related

Sumproduct Exact Match To Referenced Cell

Jan 16, 2009

I have the following formula in a cell that gives me a sum of items matching certain criteria.

View 3 Replies View Related

Sumproduct Based On Adjacent Cell ID Code

May 2, 2009

I am trying to get the sumproduct of a column of amounts based on the adjacent cells ID code.

For example column B will have codes 1FHD, 2FHS, 3FHG etc. these codes will be mixed otherwise I would have just did a sum of the cells.

Column C would have dollar amounts.

Sumproduct based on adjacent cell ID Code

View 6 Replies View Related

Single Cell Array Or Sumproduct Formula

Jun 16, 2009

To calculate the result for a month is easy, but I can't figure how to get a single cell formula to calculate for the year. The sample attached explains it better.

View 4 Replies View Related

Using Sumproduct But Need To Find Specific Word In Cell?

Jan 29, 2013

I am currently using this formula -
=SUMPRODUCT(--(MONTH(Leads!B3:B999)=1),--(YEAR(Leads!B3:B999)=2013),--((Leads!J3:J999)="Contract"))

This gives me the count of rows that have Jan as the month, 2013 as the year and Contract word in column J. Now I want to add another column to be checked. That column has values in format - Design: X, Design: Y, Design: Z etc.

So I want to have the condition above and the cells that have the word Design.

View 3 Replies View Related

Sumproduct Too Slow, Delete Seconds From Every Cell

Jun 10, 2008

I have 2 problems:

1) I am doing multiple sumproducts on 6 sheets within a workbook with last row now being in the 12,000 cell range. Since putting in this SumProduct, my excel is extremley slow and affecting other windows applications. Is there anything I can use to replace this sum product ?

=SUMPRODUCT(--(TEXT($B$4:$B$9003,"mmddyyhh")=TEXT(P2273,"mmddyyhh")),$D$4:$D$9003)

It seems to be starting the calculations even before I put any values in column D ? I have already tried the manual calculation, but it is not making much of a difference.

2) After doing the sumproduct, I then need another row to look at the value of the sumproduct cell and report it in another sheet. My problem is that the cell contains the date and time in 1 cell, but in order to get the cell to lookup the value properly I need to delete the 00 in the seconds to refresh the cell or else it gives me back an n/a# value, even though the seconds are 00 and not 01 or 02 etc ?

example : ....

View 9 Replies View Related

Modifying A Sumproduct Formula To Search For Words In A Cell

Jul 7, 2009

My current formula is this: =SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311G<>" DM "),Str311L). All the items starting with Str311 are formula's associate with a name. What I'm trying to do is add one more factor into the formula. I need it to look in column I for any cell that contains the words L/S. I tried modifying the formula to this but it does not work:

SUMPRODUCT((Str311A=1)*(Str311C=15)*(Str311I="*L/S*")*(Str311G<>" DM "),Str311L)

The formula contained inside the names are:

Str311=OFFSET(Outlet!$A$1,MATCH("STORE # 163311",Outlet!$A:$A,0)-1,0,MATCH("TOTAL FOR STORE : 163311 SAN MARCOS",Outlet!$A:$A,0)-MATCH("STORE # 163311",Outlet!$A:$A,0)+1,1)

Str311A=OFFSET(Str311,0,0)

Str311C=OFFSET(Str311,0,2)

Str311I=OFFSET(Str311,0,8)

Str311G=OFFSET(Str311,0,6)

What I need is a way to modify my original formula to now also only match cells that contain the phrase L/S. The phrase can be located at the beggining, middle, or end of the cell characters.

View 2 Replies View Related

Dividing Output Of Sumproduct Formula By Count Of Specific Cell Contents

Aug 21, 2009

I've got the following sumproduct formula (which seems to work):

=SUMPRODUCT(--(ISNUMBER(SEARCH($B17,Product_Keywords))),--(YEAR(Invoice_Date)=H$3),--(ROUNDUP(MONTH(Invoice_Date)/3,0)=H$4),--(Invoiced_Amount))

B17 is a keyword to be found in the Product_Keywords range

I'd like to modify it so that:

ifthe cell in the range Product_Keywords also includes "," thendivide the sumproduct by the number of commas+1

Presumably I could add --(ISNUMBER(SEARCH(",",Product_Keywords) as one of the conditions,
but how would I keep track of the number of commas in the cell?

View 9 Replies View Related

Sumproduct? Sumif? (search A Range Any Cell Containing Words Or Phrase "20%" )

Jun 4, 2009

I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.

I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.

View 2 Replies View Related

Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT(($O$9:$O$259="Active")+0)+SUMPRODUCT(($O$9:$O$259="Clearing Tmrw")+0) and

SUMPRODUCT(--($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259) and

SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Active"),$B$9:$B$259)+SUMPRODUCT(--(YEAR($A$9:$A$259)=$E$4)*($O$9:$O$259="Clearing Tmrw"),$B$9:$B$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

View 10 Replies View Related

Sumproduct..?

Jul 11, 2009

I have the following formula:

View 4 Replies View Related

SUMPRODUCT = #Value!

Mar 25, 2009

I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.

I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....

View 3 Replies View Related

How To Add Another Sum To Sumproduct

Jul 11, 2012

How to add another sum to this formula?

this is the formula i have at the moment

=SUMPRODUCT(--($AA$21:$AA$6376

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved