Excel 2007 :: Complex SumIf / Sumproduct Formula?

Oct 11, 2011

I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.

The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.

I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.

How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?

Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007

View 3 Replies


ADVERTISEMENT

Excel 2007 :: Determining / Counting Number Of Values Used In SUMIF / SUMPRODUCT Function

Aug 2, 2014

I am using Office/Excel 2007 and Windows 8

I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.

Example:

Results for Row #2 gives: 95 with 3 values picked up under "Rate"

Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"

How can I count the number of these values referencing at the "Rate" like in the functions used.

I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.

I tried COUNTIF function, but allows only field/heading.

A
B
C
D
E
F
G

[Code] ..........

results for each formula used

95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)

95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))

3 formula: =COUNTIF(B1:G2,B1)

View 9 Replies View Related

Excel 2007 :: Simplifying Formula With Multiple SUMIF

Feb 20, 2014

I've got a SUMIF formula that works but will be complex to expand. Excel 2007

A10= SUMIFS(b2:b4,a2:a4,">="&B6,a2:a4,"="&B6,c2:c4,"="&c6,a2:a4,"="&c6,c2:c4,"

View 6 Replies View Related

Sumproduct Or Sumif Formula

Aug 24, 2009

a1=good
a2 = 100

b1 = bad
b2 = 50

c1 = good
c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

View 6 Replies View Related

SUMPRODUCT INDIRECT & SUMIF Formula

Jul 12, 2006

I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.

1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.

2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.

3) Is it possible to use a vlookup function for this solution?

View 9 Replies View Related

Complex COUNTIFS / SUMPRODUCT Validating Dates Using 2 Ranges

Oct 4, 2013

I am trying to find out how many projects might be active as of a certain date.

I have a tab in excel that contains project data. For each project there is a "Start Date" and there might be an "End Date"

My question is, how do I count all the rows where the start date is Less than a given date and the end date is after a given date? The wrinkle is... if the end date is empty then obviously it should be counted as well (assuming the start date is still before the given date).

Here are the approaches I've used thus far:

=SUMPRODUCT(1*( AND(OR(RawMetrics!P:P > A2, RawMetrics!P:P = ""), RawMetrics!$O:$O < A2 )))
=COUNTIFS(RawMetrics!P:P,">"&Sheet1!A2,RawMetrics!O:O,"

View 4 Replies View Related

Excel 2007 :: Sumproduct And IfError

Jul 6, 2012

I need the following formula to return the sum despite having #ref errors in the ranges

=IFERROR(SUMPRODUCT(--($H$2:$H$24="E"),--($I$2:$I$24="E"),--($J$2:$J$24="E")),"")

View 1 Replies View Related

Excel 2007 :: SumIf (total Value) For Supplier Codes

Feb 6, 2012

Column O is a list of unique 1000 supplier codes

Column G is a list of 75000 imports (dollar amounts), each row has a supplier code.

I want to have a formula in Column P to sum the rows where the suppier code equals the supplier in column O.

i thought this was easier in 2007.

View 3 Replies View Related

Excel 2007 :: Using Sumproduct (or Alternative) For Hundreds Of Criteria?

Feb 13, 2013

I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.

My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.

I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.

Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?

Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.

View 1 Replies View Related

Excel 2007 :: Sumif Command Adding 2 Different Part Numbers Together?

Apr 10, 2013

I have a Excel 2007 spreadsheet of part numbers and quantities sold. In the spreadsheet we have similar part numbers, but my sumif command is adding these together. the parts are :

0124225031
R124225031

My column of part numbers is formatted as text

My formula is this =SUMIF(Sheet1!H:H,A16,Sheet1!Q:Q) where H is the part number and Q is the quanity

I tried adding a format command in the sumif command, but it returned a 0. =SUMIF(Sheet1!H:H,format(A16,"0"),Sheet1!Q:Q)

better formula and why is excel adding different part numbers together?

View 1 Replies View Related

Excel 2007 :: Crashes When Cell References Using SUMIF To Another Workfile Are Above Row 65k

Nov 28, 2013

I have 2 excel files (both .xlsx) and in the first file i am creating a reference to cells in the 2nd file using SUMIFS function. The 2nd file has 275,000 rows of data. I can create the formula correctly, however when i save the file, it just crashes every time (says not responding). If i create simple reference (=A275000) then its fine so im not sure what the issue is.

View 1 Replies View Related

SUMPRODUCT Or SUMIF?

Oct 21, 2008

ColAColBColC
1 FA4,000.00 10%
2 CB1,500.00 15%
3 FA7,500.00 15%
4 AA700.00 5%
5 JG250.00 5%
6 FA1,200.00 5%
7 AA900.00 5%
8 CB175.00 15%
9 FA2,000.00 2%

At the foot of the spreadsheet I would like to show the total of the calculation
Col B * Col C, if Col A shows a value of "FA"

Having veiwed similar posts, I believe SUMPRODUCT may be the correct formula, but I cannot seem to achieve the result I am looking for.

So in the example the desired result is 1,625 B1 * C1 + B3 * C3 + B6 * C6 + B9 * C9

View 9 Replies View Related

Sumif Or Sumproduct?

Feb 26, 2009

How can I write a conditional sum formula that will skip a criterion when it is left blank?

I was able to make this work:
=CHOOSE(COUNTA(B:B),SUMIFS(F1:F4,D1:D4,B1),SUMIFS(F1:F4,D1:D4,B1,E1:E4,B2))

where column B has the criteria.

I was wondering if there was a better way using Sumif or Sumproduct.

View 9 Replies View Related

SumIf Or SumProduct

Aug 23, 2009

in colum A i have a list of dates, in colum B i have a list of amounts. what i need to get is the sum of the amounts where the dates in colum A are between the dates in cells C1 & D1. Tries sum producst but all I get is the total sum of colum B irrelevant of the date!

View 9 Replies View Related

Sumif & Sumproduct (combined Together)

Oct 29, 2008

i would to combined these together

can this be done

View 2 Replies View Related

3 Conditions - SUMIF Or SUMPRODUCT Or...?

Jan 6, 2009

I have 4 columns, A B C and D. They are a mix of formats.

A= Name (text)
B= Date
C= Code (text)
D= Number

I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.

I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.

View 14 Replies View Related

SumIf/Sumproduct If Contains Detail

Mar 10, 2009

I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.

I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:

=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)

View 3 Replies View Related

A Sumif Of Sumproduct Question

Jul 9, 2009

I'm trying to do here is if a layaway has been paid off then there will be a tax amount in column C. IF there is tax in column C and column A has the word layaway (or the word way) in it then I need to find out how much the total of the layaway $ amount was. Meaning, to figure out how much the tax is for.

For example:
If the text in Column A contains the word way AND column C contains a $ amount then return the amount that the tax would be for. Of course this would be an accumulated amount for all of column C. So, if Layaway is in column A and $3.13 amount is in column C then it will return $50.00 as the original selling price. Tax here is .0625 %.

View 2 Replies View Related

Sumif & Sumproduct Combination

Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

View 2 Replies View Related

SumProduct / Sumif, Within 2 Times

Jan 20, 2010

From some fleet data i am trying to total any journey which took place between two times.

The time format is hh:mm:ss. I want to total the miles travelled in each trip between 5pm and 5am for certain vehicles

Vehicle Registration is in colum A, the times of the journey are column B and the miles traveled are column D.

=SUMPRODUCT(--(A2:A100=LG52RYT),--(B2:B1000=NOT Sure???, D2:D1000)

Would I use the above formular? And what would i put in the section i have marked "not sure"?

View 10 Replies View Related

Convert Sumif To Sumproduct

Dec 8, 2010

I need to convert the following formula to a Sumproduct so the link will not be broken when I close the linked workbook:

=SUMIF('Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)

View 4 Replies View Related

More Than One Criteria In SUMIF Or SUMPRODUCT,

Jan 19, 2009

I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.

View 9 Replies View Related

Sumif? Sumproduct? If? Vlookup

Jan 30, 2009

I have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?

http://s401.photobucket.com/albums/pp94/nmweir/?action=view&current=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">

direct link? :
http://i401.photobucket.com/albums/p...r/untitled.jpg

View 9 Replies View Related

SUMPRODUCT As SUMIF #DIV/0 Error

Jun 17, 2009

I am using the SUMPRODUCT formula as a SUMIF with multiple conditions in the following format:

SUMPRODUCT(-(Range1=CellReference),-(Range2>0),Range3/Range2)

THis works with or without the 2nd condtion, but I thouht the "greater than 0" bit would eliminate the inclusion of 0's; however, the same #DIV/0 error occurs with or w/out that ">0" condtion as the 1st OR 2nd argument.

I speculated that b/c the final argument was dividing each paid of cells in either range instead of a typical sum, this caused the divisor error.

View 9 Replies View Related

Sumif V Dsum V Sumproduct

May 2, 2007

I am using the SUMIF formula to sum all the cells that meet a certain criteria and at the moment, this works fine. Now I want to extend the criteria to 2(3 later) conditions. I've read that the SUMIF function only handles 1 criteria. Is this correct? If so should I use SUMPRODUCT or DSUM? I eventually will have to check about 2000 cells in a workbook. Sample work book attached: where I need to total the price of all the items that are shoes and are red in colour. A pivot table beckons in the future, but for now I need this in a workbook format.

View 3 Replies View Related

Excel 2003 :: SUMPRODUCT Date Comparison Formula

May 7, 2014

The following formula works perfectly in Excel 2003:

=SUMPRODUCT((A2:A10="A")*(b2:b10="B")*(c2:c10="C"))

However, I've got a fourth column that contains either a date or a blank cells; I want to limit the formula to only include certain dates in the sum. i.e:

=SUMPRODUCT((A2:A1000="A")*(b2:b10="B")*(c2:c10="C")*(d2:d10>="01/04/2014"))

Rather than an error it's just retuning an incorrect number.

View 6 Replies View Related

Complex Formula

Mar 15, 2008

i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:

=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99

What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05

View 9 Replies View Related

Sumproduct Or Sumif Date Range?

Jan 27, 2008

Im Trying to use a formula of countif or sumproduct

so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.

[Code] ........

But this formula doest count.

I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .

View 3 Replies View Related

Sumproduct And SumIF Commands Merged

Dec 19, 2013

I am trying to extract data all countries excluding Spain and Portugal and also exclude data for certain product classes (for example excluding Products X, Y and Z)

I have written the formula below which picks up data for certain criteria I have concatenated in tab "raw data" in column A:

=SUMPRODUCT(SUMIF('Raw Data'!$A:$A,B7&D3,'Raw Data'!$W:$W)=('Raw Data'!I:I"Spain")*('Raw Data'!I:I"Portugal"))

Notes: Cells B7 and D3 are used match criteria in Raw Data A:A
Column W is the data that I need to extract i.e the numbers
Raw data I:I is a list of all countries

However this does not exclude Products X, Y and Z, only excludes countries Spain and Portugal.

I wondered if there was an easier way to create a formula?

View 4 Replies View Related

SUMPRODUCT / SUMIF With Named Range

Mar 26, 2007

Why won't this SUMPRODUCT work?

SUMPRODUCT(Sheet2!D1:D2000=OPEN,Sheet2!F1:F2000)

I have a range (D1:D2000) that contains a 'Status' value.

OPEN is a named range that refers to eight possible Status values: Assigned, New, Hold, Re-test, In Progress, Failed, Ready for Push, Coded

The formula should sum the values in F1:F2000 for any row containing one of the eight statuses in D1:D2000.

View 9 Replies View Related







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