# 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
ADVERTISEMENT
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
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
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
Oct 29, 2008

i would to combined these together

can this be done

View 2 Replies
View Related
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
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
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
Oct 9, 2009

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

View 2 Replies
View Related
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
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
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
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¤t=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
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
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
Mar 7, 2007

Below I have a formula which for some reason or another I cannot get to work.

It is an array formula and it as worked before.

I am not too sure if the reason for the formula not working is because there is a formula in each of the columns that it is looking up.

And I keep getting the result of #N/A.

The formula which I am having problems getting it to work is;

=SUM(IF($D$3:$D$43=$B$57;IF(I$3:I$43="~*~";1;0);0))

View 9 Replies
View Related
Feb 17, 2009

I have two columns with numbers and want to build the sum of them with a condition.

These are stock prices. So prices go up and down and I can go long or short.

I have a column that indicates if I went long (l) or short (s).

If I go long (l), and its a winner, I want to have the sum of Exit Price - Entry Price.

If I go short, and its a winner I want to have the sum of Entry Price - Exit Price.

If I go long (l), and its a loser, I want to have the sum of Entry - Exit.

If I go short (s) and its a loser, I want to have the sum of Exit - Entry.

Overall I just want to make sure that the sum has the right algebraic sign (+ or -), depending on whether it was long or short and whether it was a winner or a loser.

View 9 Replies
View Related
May 25, 2007

I need to sum amount if several criterias are met. I have attached an example of the report I need to reference to. If column A=724 and column B=ZZZ I want to sum on the amount in column C next to ZZZ. The problem is that ZZZ is not always in the same cell, or it's not even in the report if it has no amount. Is it possible at all to sum on those complicated criterias,

View 5 Replies
View Related
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
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
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
Mar 27, 2009

-I have 3 relevant columns, A, B and C with 999 rows

-Column A is a status indicator "Yes" or "No" are the only options

-Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*"

-Column C is the data range that needs to be summed

Right now i have this:

=SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)

But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.

View 9 Replies
View Related
Jul 2, 2009

Is there a way to replicate this code, so I don't have to Do the With [CODE] .......

View 9 Replies
View Related
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
Jan 4, 2007

I'm trying to get a sum only if 2 conditions are met.

Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.

What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...

=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))

but it's returning zero values regardless of the criteria being met or not.

View 12 Replies
View Related
Sep 20, 2005

I have the array formula:

=SUM(IF((Sheet1!$Y1:$Y5809="26")*(Sheet1!$AJ1:$AJ5809=CREDIT!$A4),1,0))

It works fine except I want it to reference the cell that the number 26 is in rather than having to have it in quotes, i tried this, but don't get the same answers as this way. The reason being is more conditions may be added in the future which would mean for example the 26 could change to a 34 etc so i rather not have to have someone manually change that number in the formula and just have it refence a cell.

View 9 Replies
View Related
May 31, 2006

My book is telling me that I can't use multiple conditions with a SUMIF

statement but other sources are telling me I can. Either way, it's not

working!

My situation:

Look at data in $V$2:$V$144 (range = "group"). If cells in "group" =

"Broth" then look at $Y$2:$Y$144 (range = "bldg"). If "bldg" = "1" then sum

corresponding cells in $R$2:$R$144 (range = "fcst").

This is one of my attempts - feeble...

=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))

View 9 Replies
View Related
Jan 25, 2012

I am trying to use multiple "ifs" and then summing the cells that obey my conditions.

Let me give an example:

Column A - place

NY

Chicago

Los Angeles

San Francisco

Column B - object

gate

keyboard

pencilcase

cellphone

[code]....

I want the ifs and sumifs to:

Check if place = NY

Check if object = keyboard

Check if condition = failure

and sum the prices when all of the above are true.How do I do it??

View 4 Replies
View Related
Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).

=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

View 2 Replies
View Related
Apr 10, 2008

My sumproduct has multiple conditions - is there a limit to the number of multiple conditions one sumproduct formula can have? I didn't think there was????

The formula looks like this, and should return results - at the moment, it returns #N/A. Does it have anything to do with the fact that I'm using named ranges?

=SUMPRODUCT(--(Data!W:W>=Cumulative!A12)*(Data!D:D=Super)*(Data!E:E=Region)*(Data!Q:Q=EWC)*(Data!J:J="H")*(Data!L:L="Tonnes"),Data!K2:K65536)

View 9 Replies
View Related