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


ADVERTISEMENT

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

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

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 Array Reference Causing #N/A

May 27, 2009

Using Excel 2003, I'm summarizing info in one sheet (Review) with data in another worksheet (Work Orders). The following SUMPRODUCT calc worked fine...

View 2 Replies View Related

Sumproduct Formula With Date Reference Not Working

Mar 7, 2012

I have this formula below that counts all matching cells that fall between two dates (a Monday to the next Monday) where S2 and T2 are the dates (one week apart).

I have 52 colums ie one for each week.

=SUMPRODUCT(($G$3:$G$1000>=S2)*($G$3:$G$1000

View 7 Replies View Related

SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

View 3 Replies View Related

Use Text In A Cell For A Formula As Cell Reference To Create Range

Mar 10, 2013

I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.

Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items

How can i use the text in those cells to put in a formula to call that as a range?

If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell

View 4 Replies View Related

Count If To Reference Cell With Text?

Aug 21, 2013

I've seen a lot of other posts on here regarding referencing a COUNTIF statement to a cell instead of a value (like this: =COUNTIF(B7:E7,"<=" & B10), but need to do it for text instead of numbers. This is basically what I've got:

A
B
C

Missy 342
Missy
4

[Code].....

Column B is created using a search function, and returning only the Missy or Mike, (using a search function, which I know how to do), and then I want the counts to populate in column C based on how many times either Missy or Mike pops up in the range.

Problem is, when I try and write a formula like =COUNTIF($D$2:$D$9,VALUE(E2)) it doesnt work, but if I put in =COUNTIF($D$2:$D$9,"*Missy*") it works (of course).

Is there any way to get it to reference the text in the cell since the text in the search criteria will constantly change?

View 4 Replies View Related

Cell Reference In Middle Of Text

Jul 10, 2009

I'm trying to create a VB Script for an AS/400 app. It's all written but I'm trying to feed the data for part from an excel file. If B5 was 10 I need the final output in my cell to read:

autECLSession.autECLPS.SendKeys "10"

Here is my current formula ="autECLSession.autECLPS.SendKeys"""""&B5*100&""""""
It's reading as autECLSession.autECLPS.SendKeys""10"" which has two sets of quotes not just one and no space between sendkeys and "10"

View 3 Replies View Related

Cell Reference Cell Then Append Specific Text Using Vb

May 16, 2009

This is probably really easy but I don't know what I'm doing wrong.

View 2 Replies View Related

Convert Cell Reference Text To Cell Formula

Aug 18, 2009

I am wanting to convert a cell reference text
"=$A$1"
to an actual cell reference
=$A$1

Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.

I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.

I'm NOT wanting an external cell to convert it for me
i.e. INDIRECT(CELL)
because I am wanting to copy the answer to another independent spreadsheet

I'm NOT wanting to paste values
i.e. return the answer from cell $A$1
because I want the cell reference to remain within the cell.

View 3 Replies View Related

Combining Text And Cell Reference With Indirect

Oct 2, 2008

I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")

But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.

View 2 Replies View Related

Cell Text As Part Of External Reference

Apr 2, 2007

I am building a summary spreadsheet which will bring in a number of values from other spreadsheets located in folders on a network. Each set of external references will return the same cells, but the location and filenames are different. In order to make it quick and easy to modify the file names and locations without having to change the cell references in my summary sheet i want to type the full reference in a cell as text then use this in another cell as part of the external reference...

View 9 Replies View Related

Match Cell Reference Rather Than Specific Text Or Value - N/A Error

Dec 7, 2013

I'm getting an N/A error on my MATCH formula.

I'm trying to match a cell reference, rather than a specific text or value.

Here is the syntax I used. MATCH(Cell,Table Array Name, 0)

I have made sure that the text matches exactly and the cell formats are the same.

View 1 Replies View Related

Look Through A Sheet, Find A Text Reference, Then Bring Up Something In A Cell

Jan 13, 2008

I'm in the leadup to my A2 ICT controlled conditions piece where i have to make an Excel system. My chosen subject for this is to use the school house points system. I'm going to make a system where the results of house matches are inputted and points are awarded accordingly.

I just have a couple of queries after looking at my initial designs.

1. Is it possible to get excel to look through a sheet, find a text reference, then bring up something in a cell located in the same row? For example, for it to locate one of the House team names, then go along the row and find the ammount of house points they have in order to make a total for them. Is this also possible over a number of sheets? (In order to keep a running total on a separate sheet).
E.G. House, Games Played, Wins, Losses, Draws, House Points

2. For the house cross country event they use a different system which differs from everything i have designed. What i basically need to do is be able to use a drop down against each finishing position where you can select one of the four house teams, then it takes the points won for that finishing position and puts them into a table.

View 12 Replies View Related

How To Reference Cell Values In A Cell Containing Only Text

Jul 24, 2014

Here is a made up scenario which tries to emulate my query:

I have a spreadsheet with two tabs - one called Model and the other called Prod. In the Prod tab, I have the following text entered in single cell - A1

My name is
I was born in
I Live in

What I would like to do is add cell values from the Model tab at the end of each line. For example;

My name is JOE
I was born in 1915
I Live in AUSTRALIA

I tried the following but it didn't work.

My name is Model!A1
I was born in Model!A2
I Live in Model!A3

I believe it didnt work was because the cell starts of with text rather than a formula

View 7 Replies View Related

Sumproduct With Value Or Text

Nov 27, 2013

My formula works fine:

In cell D12:
=SUMPRODUCT(('Raw data'!$L$25:$L$10958=Sheet1!$C$9)*('Raw data'!$C$25:$C$10958=Sheet1!$C12),('Raw data'!$H$25:$H$10958))

Sheet1!$C$9 = "Both"

But when i change C9 to i.e. 2013 or 2012....the data does not show anything only 0s?

i have attached a sample : sumprod_values_issue .xlsm‎

View 11 Replies View Related

Sumproduct & Text

Feb 5, 2007

I am trying to use the following formula:
=SUMPRODUCT('Raw Data'!$B$2:$B$10000=$A$1)

The reason why I am using sumproduct is b/c I have to add more to this formula. In column B on the Raw Data sheet there are years listed. The years are formatted as text. Is there a way to have this formula work without having to change the format of column B?

View 10 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

Sumproduct For Numbers Within Text

Jan 16, 2014

How to use Sumproduct for the following?

Col1 Col2
5 1 mL
4 1.5 mL
5 2.5 mL
7 2 mL
2 1.5 mL

View 7 Replies View Related

Sumproduct With Text String

Aug 4, 2008

I have trying to write a sumproduct formula, however, the total sum amount that i get is "0", which i think the formula is not picking up the text.

=SUMPRODUCT(($Y$2:$Y$65536="Evaporative cooler*")*($Q$2:$Q$65536=$AM$5)*($N$2:$N$65536=6),($AA$2:$AA$65536))

Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?

There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.

View 14 Replies View Related

Sumproduct To Find Text

Nov 18, 2008

I recently had a lot of help using the sumproduct formula with isnumber datevalue added to it. I now want to do the same but rather than finding numbers, i want it to find text.

In my attached example. You can see that Jerry A has received three applciations, but 2 of them have been cancelled. How can i add the formula where it would only count Jerry A's applications, but where they have been cancelled i.e. the formula would show 2 not 3.

View 6 Replies View Related

Sumproduct #Value! Error Due To Text

Feb 26, 2007

I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT(($C7:$C16=$C$2)*($D7:F16))

I've tried =SUMPRODUCT(--($C7:$C16=$C$2),($D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

View 9 Replies View Related

SUMPRODUCT Contains Text, Using Asterisk

Nov 9, 2009

Let's say I have two columns: Homeless (column A) & Services (column B). Homeless responses can be either "Y" or "N". Service can be "a", "b", or "ab". Here is an example:

Homeless Service
y a
y b
n a
y ab

I was hoping to use SUMPRODUCT to calculate clients who are homeless and receiving "a" service. I want to be able to calculate those receiving "a" service even if they are receiving "b" service, so I attempted to use asterisks like I have used prior with the COUNTIF function. Here is what I thought would work:
=SUMPRODUCT((A2:A5="y")*(B2:B5="*a*"))

And I expected it to return 2, but I found that I couldn't get the SUMPRODUCT to work with asterisks. I am able to get the results I want be creating a new column to sort the services into just those who are homeless and then using COUNTIF, but I'd rather come up with a more streamlined approach.

View 3 Replies View Related

Sumproduct To Return TEXT Value?

Dec 23, 2013

I written the formula below:

=SUMPRODUCT((B7:B9=$A1)*(E7:E9>=0),"CAP EXCEEDED","CAP Not breached")

B7:B9 = text and numerical values in format Year 1, Year 2 etc
E7: E9 = numerical values

what I am trying to achieve is if YEAR 1 for example is equal to cell A1 and the value in column E is greater than 0 then return statement "CAP Exceeded else return the statement CAP not breached.

View 6 Replies View Related

SUMPRODUCT To Return Text

May 1, 2008

The below table is a small extract from my data. I want the formula in C3 to return the contents of A4, that is, where Amount = D3, Policy = E3, Status A3. The following formula returns 0, because I'm trying to return a text string rather than a numerical value:....

View 9 Replies View Related

Wildcard For Text In Sumproduct

Aug 14, 2008

Tring to get the following spread sheet to calc column C using two criteria
Criteria 1 Column D must contain HC Investor anywhere
Criteria 2 Column A must be less than Reimbrusement - 11
The following formula in G2 appears to have an error in criteria 1 ....

View 9 Replies View Related

Text Wildcard In Sumproduct

Jun 15, 2009

I am having great difficulties in finishing this formula off.
It is a sumproduct containg many criterias, however one of them is to only add up a certain coloumn if another column begins with L&L...

The coloumd contains L&L and a number, but the number changes so i need the number to be a wildcard. I have tried ="L&L???" but this does not work.

=-SUMPRODUCT((Catagory="STK Retail")*(MONTH(Inv_Date)=3)*(YEAR(Inv_Date)=2008)*(How"Account")*(Order="L&L???")*(Stock))

View 9 Replies View Related







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