Free Microsoft Excel 2013 Quick Reference

Sumifs with multiple values for one criteria range Results

I am using Excel 2007 to analyse soem data. I need to get the total amount based on some criteria. I know SUMIFS can be used for this. Please see the attached excel sheet. I am using the below formula to calculate the total amount.
=SUMIFS(C2:C16,A2:A16,"Jan",B2:B16,"Fri")+SUMIFS(C2:C16,A2:A16,"Jan",B2:B16,"Mon")+SUMIFS(C2:C16,A2: A16,"Feb",B2:B16,"Fri")+SUMIFS(C2:C16,A2:A16,"Feb",B2:B16,"Mon")

This is only an example. The actual calculation I am trying to do involve more than 500 rows. So I need a better solution.

This is what i am looking for:
SUM Amount if ( (Month is equal to 'Jan' or 'Feb') AND (Day is equal to 'Mon' or 'Fri')

That is, a formula in which i can give multiple values for one range. Can some one help me with an Excel 2007 formula for this?

Good afternoon,

First of all, thank you for this forum that I've been reading from time to time, and helped me to solve Excel issues without the need to create new topics... Thank you for this.

For this one I couln't find any solution to my problem. Maybe someone can help me ?

After extracting data from Business Objects to my Excel spreadsheet (accounting data from various companies), I would like to consolidate (sum) data from several companies.

In rows I would need to use criterias like different accounts numbers (ex : "4110000" + "4861200") AND in colums, I would need to match criteria "company names" (ex : "CIE_1" + "CIE_2").

As far as I'm now, I found 2 possible ways (any other solution you could propose is warmly welcome):

1/ SUMPRODUCT : I managed to obtain a working test formula, but it gets far too complicated to use it as I will need about 25 to 30 criterias from columns and 15 to 20 criterias from rows, coming from different spreadsheets (this formula would be working but you can figure how long to type it would be, as I need to build many of those and source of errors if any modification is needed someday)

2/ SUMIFS : This formula would be perfect IF I could manage to get a one that works in my case.

Here is the formula I build, but it is not working now (returns #VALUE!)


If it can help, here are the 2 problems I could identify in my formula, but was not able to solve :
* How can I select a sum_range wider than a simple colum (here the range I need is C8:G20). It seems that SUMIFS fonction returns #VALUE! on wide range even with basic tests.

* I need to select criterias from rows (ex : some accounts in B8:B20) AND columns (ex: some companies in C7:G7).
Here also, it seems that SUMIFS fonction returns #VALUE! on cross criterias rows/columns, even with basic tests.

I joined my excel file, so it will be clearer for anyone who'd like to help...

Please help me to get this formula working, otherwise any other solution is welcome to get the consolidated figures I need...

Thank you for your time and advice

I am trying to sum values in a one column if the dates in a row fall within
a date range. There may be multiple occurrences of dates that occur in that
given range and therefore the value in the sum column needs to be added as
many times as that occurance. I have tried to to use a sumifs statement but
it seems only to be adding the value if it appears once in that row. I have
tried to add a table below that illustrates the question but I am not sure
the formatting will show. Any help will be greatly appreciated. I have
excel 2007.

Col A Col B Col C Col D
row 1 Units Price Sold
row 2 4 $20 Jan-10 Feb-12
row 3 2 $5 Jan-09 Jan-10
Row 4 3 $7 Jan-10 Feb-10

The results I need are as follows ( Just sums all the occurances of a unit
being sold for the date range)

2009 2010 2011 2012
Sales $5 $39 $- $20

I used this formula for the year 2010 and made it an array

=SUMIFS(b2:b4,c2:d4,">="&Date(2010,1,1),c2:d4,"

Good Day,
I'm trying to find an easier way to perform the same function below:

=SUMIF($C$8:$C10,"=67610",E$8:E10)+SUMIF($C$8:$C10,"=67615",E$8:E10)

Basically, I have a column with a list of account numbers, and I need to perform a few different "SUMIF" formulas where the criteria contains more than one item. The column with the amounts to then sum up is my sum range. For example, Column A has a list of Account numbers from Rows 1 - 10, Column B has amounts associated with each account in column A. How can I do one SUMIF or equivalent formula, in order to sum the values of two or more Amounts based on two or more Account Values in Column A.

Hi! I need a formula with an idea simular to that of SUMIF, but I need several "IF" and several criteria for each of them. Let me describe, here is the data:

Name Plant Room Stock
A 2110 1010 1,000
A 2110 2,000
A 3010 3010 3,000
A 2130 2010 4,000
B 2120 1020 5,000
B 2120 2020 6,000
B 2130 7,000
B 2130 1010 8,000
B 2130 2010 9,000

In the other table I want to find out, what is the total stock of A that is on plants 2110, 3010 in rooms 1010,"", 2020. (There are several rooms in each palnt).

so formula must contain this logic: Sum of Stock if Name is A and Plant is 2110 or 3010 and rooms are 1010,"" or 2020.

I found a solution via massive formula, but the real situation is so that there are 500 names, 50 plants and 50 rooms, and I may need up to ten criterias for plant and the same for room, as the data consists of 10000 lines, and I need the outpoot of total stock for each name on certain plants in certain rooms.

In this situation massive megaformula becomes too long to fit in one cell, moreover calculation is too long.

The best result is to create (write in basic or whatever and implant it in excell) a fomula like SUMIF with several possible criterias and ranges of values that fit each of them.

Is it somehow possible?

If the situation is not clear enough, I can provide the massive formula that is the solution for now.

Unfortunately my longer message on this got lost in space after I had timed out from the forum. But here is a quick recap:

The situation
I have a database that has all the purchases made from us. On rows are individual deals, and on colums I have: Year, Buyer 1, Buyer 2, Buyer 3...Buyer 8, Value, Region, Category...

What I need to do, is a table that has on rows, different client names and on colums for example years or regions. In the cells I need to sum the total value client X has purchased in year Y or from region Z.

To do this, I entered a sumifs-formula with multiple further criteria (it can't be a specific category or value has to be larger than something...). This works fine when I do it for column "Buyer 1" but because I want it to sum up the deal values whenever Client X is in any of the colums from Buyer 1 to Buyer 8, it doesn't. My "short"-cut was to copy paste the sumif-formula multiple times so that there is one for each buyer column. Obviously this makes the sheet slow and difficult to run or update.

Therefore is there a way of doing this within one sumif-formula?

Unfortunately I cannot paste a sheet right now, because my excel is stuck (because of this issue) but I'll do it as soon as possible if needed.

Thank you for any help you can provide!

This is my first foray into Ozgrid, so please forgive any inefficiencies I may have in presenting my problem. I’ll be happy to clarify further if needed and/or post sample files on request.

I’m relatively new at using the INDIRECT function, and am having a hard time setting up the syntax for ranges, and even knowing if those ranges will work.

I have a workbook with multiple sheets (let’s call them Program sheets) created from a template that contains variable numeric data that I need to sum by creating a formula on a Summary sheet within the same workbook. The criteria for IDing and summing the data from the Program sheets is spread over 3 cells in adjacent columns (let’s call them $E7, $F7 and $J7) on the Program sheets. A string concatenation of these cells will not create a unique string value on any one sheet as there are potentially multiple rows of data on each sheet and across sheets that could have the same value string. The Summary sheet is a report that contains hard-coded values in adjacent cells ($C4, $D4 and $E4) that will match values found in columns E, F and J from the Program sheets.

I’d like to have the formula sum all values within the range P7:AA70 across all the Program sheets when the entries into E, F and J cells (from Program sheets) match $C4, $D4, and $E4 cells on the Summary sheet, keeping in mind that there could be multiple instances of the same values over several rows within the Program sheets (that’s OK, because I want each instance to be part of the sum)

Here’s a formula I created for summing values found in a range based on a single matching criteria across sheets. Can this be adapted to the new sum formula I need?

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheet_List&"'!G7:G70"),'By-Month Summary'!$G7,INDIRECT("'"&Sheet_List&"'!P7:P70")))

Sheet_List is a named range on a separate tab that lists the names of the Program sheets that I need to sum from.

P7:P70 is the range that the sumable data lies in.
G7:G70 is the range that contains values that need to match the criteria on the By-Month Summary sheet cell G7.

For the new formula, I no longer want to sum based on criteria in the G column, but rather on criteria in the multiple columns I outlined in my diatribe above.

After about a year and a half in this forum, I have found the most common question to be... How can I count or sum multiple criteria. Like Countif or Sumif, but having 2 or more columns with criteria. So I thought I'd take some time to write out an explaination. This is going to be long winded. I'm going to try to go into the greatest detail I can.

We'll first need to understand the basic function of Sumproduct. How it is intended to be used.

******** ******************** ************************************************************************>Microsoft Excel - Personal.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD2E2F2D3D4D5D6D7D8D9D10=
ABCDEF1Val1Val2 Val1 * Val2Sum of DSumproduct2220 40384038403330 90  4440 160  5550 250  6660 360  7770 490  8880 640  9990 810  1010100 1000  Sheet2 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In D2 I've put your basic Multiplication formula
=A2*B2 and filled down to row 10
In E2 is
=SUM(D2:D10)

Sumproduct does that for you in 1 formula
Sumproduct SUMS the Product of Each argument(seperated by commas).

in F2
=SUMPRODUCT(A2:A10,B2:B10)

You should see E2 and F2 have the same result.

How it works...
It reads the formula 1 row at a time.
So starting in row 2, it multiplies A2*B2 = 40
Now to row 3, it multiplies A3*B3 = 90
Now to row 4, it multiplies A4*B4 = 160
etc...
That is the PRODUCT part of SUMPRODUCT.

THEN it ADDS all the results from each row.
that is the SUM part of SUMPRODUCT.

That is the basic intended purpose of SUMPRODUCT

Now, how does that help us with Multiple Critera Count/Sum...

We'll start with COUNT

Now look at this example sheet

******** ******************** ************************************************************************>Microsoft Excel - Personal.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE5E8=
ABCDEF1NameProductCost MakerProduct2FordCar12713 ChevyCar3ChevyTruck10816   4ChevyVan10503 COUNT 5FordCar11552 3 6ChevyCar13887   7FordTruck10451 SUM 8ChevyCar14579 42001 9FordVan11395   10ChevyCar13535   Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Take the formula in E5
=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2))

The formula is devided into 2 sections(or arguments seperated by commas)

--(A2:A10=E2)
--(B2:B10=F2)

These are QUESTIONS, also called EXPRESSIONS.
They each result in TRUE or FALSE.
Remember that it reads the formula 1 row at a time.

Does the value in A2 = The value in E2 ? TRUE or FALSE ?
Does the value in B2 = The value in F2 ? TRUE or FALSE ?

But SUMPRODUCT needs NUMERICAL entries, not Text or TRUE/FALSE..
That's where the -- comes in.

In Excel, TRUE = 1 and FALSE = 0
So that formula can be broken down like this (using Row 2 as example)
Does A2 = E2 ? FALSE
Does B2 = F2 ? TRUE
so the formula is translated from
=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2))
to
=SUMPRODUCT(--(FALSE),--(TRUE))

The -- converts TRUE to 1 and FALSE to 0
so the formula is further translated to
=SUMPRODUCT(--(0),--(1))
and that also removes the -- and extra brackets
=SUMPRODUCT(0,1)
So now Row 2 is
0 * 1 = 0

Now moves to Row 3
Does A3 = E3 ? FALSE
Does B3 = F3 ? TRUE
so the formula is translated from
=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2))
to
=SUMPRODUCT(--(TRUE),--(FALSE))

The -- converts TRUE to 1 and FALSE to 0
so the formula is further translated to
=SUMPRODUCT(--(1),--(0))
and that also removes the -- and extra brackets
=SUMPRODUCT(1,0)
So now Row 3 is
1 * 0 = 0

And it continues down each row.
Each row has only 2 possible results.
1 or 0
Because, sumproduct multiplies each argument.
x * x * x etc...
if ANY of those #s are 0, the result is 0
If ALL of those #s are 1, the result is 1

Then after it has done that to all rows,
it then SUMS the result of each row.

In the example from the above table..
only rows 6 8 and 10 would result in 1, all others result in 0.
I'll break down each row

=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2))

One row at a time

Row 2) SUMPRODUCT(--(A2=E2),--(B2=F2))
Row 3) SUMPRODUCT(--(A3=E2),--(B3=F2))
Row 4) SUMPRODUCT(--(A4=E2),--(B4=F2))
Row 5) SUMPRODUCT(--(A5=E2),--(B5=F2))
Row 6) SUMPRODUCT(--(A6=E2),--(B6=F2))
Row 7) SUMPRODUCT(--(A7=E2),--(B7=F2))
Row 8) SUMPRODUCT(--(A8=E2),--(B8=F2))
Row 9) SUMPRODUCT(--(A9=E2),--(B9=F2))
Row 10) SUMPRODUCT(--(A10=E2),--(B10=F2))

Converted to TRUE/FALSE

Row 2) SUMPRODUCT(--(FALSE),--(TRUE))
Row 3) SUMPRODUCT(--(TRUE),--(FALSE))
Row 4) SUMPRODUCT(--(TRUE),--(FALSE))
Row 5) SUMPRODUCT(--(FALSE),--(TRUE))
Row 6) SUMPRODUCT(--(TRUE),--(TRUE))
Row 7) SUMPRODUCT(--(FALSE),--(FALSE))
Row 8) SUMPRODUCT(--(TRUE),--(TRUE))
Row 9) SUMPRODUCT(--(FALSE),--(FALSE))
Row 10) SUMPRODUCT(--(TRUE),--(TRUE))

Converted to 1/0 with --

Row 2) SUMPRODUCT(0,1)
Row 3) SUMPRODUCT(1,0)
Row 4) SUMPRODUCT(1,0)
Row 5) SUMPRODUCT(0,1)
Row 6) SUMPRODUCT(1,1)
Row 7) SUMPRODUCT(0,0))
Row 8) SUMPRODUCT(1,1)
Row 9) SUMPRODUCT(0,0)
Row 10) SUMPRODUCT(1,1)

Simplest Math Expression

Row 2) 0 * 1 = 0
Row 3) 1 * 0 = 0
Row 4) 1 * 0 = 0
Row 5) 0 * 1 = 0
Row 6) 1 * 1 = 1
Row 7) 0 * 0 = 0
Row 8) 1 * 1 = 1
Row 9) 0 * 0 = 0
Row 10) 1 * 1 = 1

Summed together
0+0+0+0+1+0+1+0+1 = 3

I hope that makes sense, and I haven't missed anything...

To make this a SUM instead of count, simply add the SumRange at the end.
See Formula in E8
=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2),C2:C10)

the last argument C2:C10 is not an expression, it's just a number.
so no -- is needed. Now the result of that row, will be the result
of the questions * value in C..

I'll break that down 1 row at a time again

=SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2),C2:C10)

One row at a time

Row 2) SUMPRODUCT(--(A2=E2),--(B2=F2),C2)
Row 3) SUMPRODUCT(--(A3=E2),--(B3=F2),C3)
Row 4) SUMPRODUCT(--(A4=E2),--(B4=F2),C4)
Row 5) SUMPRODUCT(--(A5=E2),--(B5=F2),C5)
Row 6) SUMPRODUCT(--(A6=E2),--(B6=F2),C6)
Row 7) SUMPRODUCT(--(A7=E2),--(B7=F2),C7)
Row 8) SUMPRODUCT(--(A8=E2),--(B8=F2),C8)
Row 9) SUMPRODUCT(--(A9=E2),--(B9=F2),C9)
Row 10) SUMPRODUCT(--(A10=E2),--(B10=F2),C10)

Converted to TRUE/FALSE (and putting in the number from C)

Row 2) SUMPRODUCT(--(FALSE),--(TRUE),12713)
Row 3) SUMPRODUCT(--(TRUE),--(FALSE),10816)
Row 4) SUMPRODUCT(--(TRUE),--(FALSE),10503)
Row 5) SUMPRODUCT(--(FALSE),--(TRUE),11552)
Row 6) SUMPRODUCT(--(TRUE),--(TRUE),13887)
Row 7) SUMPRODUCT(--(FALSE),--(FALSE),10451)
Row 8) SUMPRODUCT(--(TRUE),--(TRUE),14579)
Row 9) SUMPRODUCT(--(FALSE),--(FALSE),11395)
Row 10) SUMPRODUCT(--(TRUE),--(TRUE),13535)

Converted to 1/0 with --

Row 2) SUMPRODUCT(0,1,12713)
Row 3) SUMPRODUCT(1,0,10816)
Row 4) SUMPRODUCT(1,0,10503)
Row 5) SUMPRODUCT(0,1,11552)
Row 6) SUMPRODUCT(1,1,13887)
Row 7) SUMPRODUCT(0,0),10451)
Row 8) SUMPRODUCT(1,1,14579)
Row 9) SUMPRODUCT(0,0,11395)
Row 10) SUMPRODUCT(1,1,13535)

Simplest Math Expression

Row 2) 0 * 1 * 12713= 0
Row 3) 1 * 0 * 10816= 0
Row 4) 1 * 0 * 10503 = 0
Row 5) 0 * 1 * 11552 = 0
Row 6) 1 * 1 * 13887 = 13887
Row 7) 0 * 0 * 10451 = 0
Row 8) 1 * 1 * 14579 = 14579
Row 9) 0 * 0 * 11395 = 0
Row 10) 1 * 1 * 13535 = 13535

Summed together
0+0+0+0+13887+0+14579+0+13535 = 42001

That pretty much wraps it up. You can add MANY criteria,
just add another section seperated by a comma using format
--(Range=Criteria)

You would be limited to 30 criteria for COUNT, 29 for SUM.

You can use other operators, like < > or for NOT equal.

You can put other functions inside the expressions, as long as
the result is TRUE false...

Like --(LEFT(A1:A10,1)="F")

that works fine, as long as the expression returns TRUE or FALSE.

Firstly.. hi and thank you for reading this post.

Elements in this equation..
Source Sheet containing a list of equipment and values related to it.
Destination sheet that i want to pull data into and add the data pulled from source.
Destination sheet has a selection cell(X) that has a validation list containing the complete discriptions colomb a of equipment discriptions in source sheet.
Destination range data, this is the area that data will be pulled in from source sheet to destination sheet based on criteria selected in cell X. This will contain Equipment sourced from in colom a and values sourced from colomb E

Below the selection cell (X) i want to pull in the discriptions and values into the (Destination Range data) based on what item I selected in the validation list. Cell (X)

So ..as i said I have a source sheet containing discriptions and values of different costs witin the business. Discriptions in colomb a and Values in E
There are many costs with the same discpn but have different cost values.
Like egines , wheels, fuel, but each one has a different cost value to it

then i want change a cell(X) in destination sheet that cotain the equipment list, I want it to look for all values that has that discription and return all elements value that is = to that discrip tion along with its value in colom E. as an extraction. And place it in destination range in the destination sheet.

So i figure it will look something like this...
If Cell X = Engines then do a vlookup for Engines in source sheet and return in destination cheet and pull in the value associated with it in theat row...

the result can either be a one row liner that returns Engines with a sum of all the engines or a multi row formula that reurns each one of the vlookups as a single value. you will then have multiple rows with all engines there are 5 rows ir there was only 5 engines.

So the furmula will be in the destination data range and i should then just copy the formula down to make sure that it can facilitate a large return of equipment ...

If anyone can help me with this i will REEEEEAAAALy appreciate it... I am almost tempted to pay for this help.. .

Thanks
CDF..
I will crown you EXCEL GURU GOD if anyone can solve this.

I just realized after I posted this that if I add another column next to A
with the same values and do SUMIF(A1:B3,"=1",C13) then I get my intended
result, but is there a way to do this without adding that extra redundant
column? Or is Excel just not smart enough to tell that when you only give it
one column for your criteria to use that for all sum columns. Oh well...

"Stefan Wrobel" wrote:

> I have a very simple task and I don't get why SUMIF isn't doing what I expect
> it to. Observe this set of data:
>
> 1 15 4
> 0 3 2
> 1 1 8
>
> If I do SUMIF(A1:A3,"=1",B1:C3) then I just get 16, which is the result of
> SUMIF(A1:A3,"=1",B1:C3), rather than 28, which is the sum of 15+4+1+8. I
> realize that I could do SUMIF(A1:A3,"=1",B1:B3) + SUMIF(A1:A3,"=1",C1:C3),
> but that just seems silly, since SUMIF takes in a Range, I don't get why it
> doesn't actually sum over the range. And yes I have tried using
> Ctrl+Shift+Enter when entering it, no help.

Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

I have 3 colums: A-dealer B-Date C-sale
I want to sum (Pivot Table is not suitable for this part, I use it on a
different worksheet) All sales made be a dealer based on a month.

Asia 9/28/2004 2134
Asia 9/30/2004 2136
Europe 10/1/2004 2137
Europe 10/4/2004 2138
Europe 10/12/2004 2139
Europe 10/12/2004 2140
Asia 10/17/2004 2141
Europe 10/18/2004 2142

The sum would go to a different sheet
MONTH Dealer Total Sales
September Asia ?????
October Europe ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:

> Hi
> first don't replace the '--'. They coerce the boolean values to real
> numbers (TRUE=1/FALSE=0)
> for your example if you want to get the sum for one specific account
> use
> =SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
> 14000="michigan"),F1:F4000)
>
> Note: this is case sensitive. You may also consider using a pivot table
> for this:
> http://www.cpearson.com/excel/pivots.htm
> http://peltiertech.com/Excel/Pivots/pivotstart.htm
> http://www.contextures.com/xlPivot02.html
> http://www.ozgrid.com/Excel/excel-pivot-tables.htm
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> dave wrote:
> > Frank,
> >
> > Sorry, I got an error. What do I put in where the "--"
> > are?
> >
> > Here is a more accurate example of my worksheet.
> >
> > Column A lists a range of account numbers so I'd like this
> > range to find all 4000 accounts.
> >
> > Column C lists a range of departments so I'd like to
> > find "collections"
> >
> > column D lists the location so I want "michigan"
> >
> > Column F has the balances so I want the sum of every
> > account that meets these 3 criteria.
> >
> > Thanks,
> >
> > Dave
> >> -----Original Message-----
> >> Hi
> >> use SUMPRODUCT instead. e.g.
> >> =SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >>
> >> Dave Gremaud wrote:
> >>> Does anyone use this function or have any suggestions on a
> >>> different function that may work. I have a very large
> >>> worksheet with multiple data columns. I am trying to sum
> >>> multiple cells by using multiple variables. The SUMIF
> >>> function works well but I can't insert more than one
> >>> criteria. I have tried many times without success. I
> >>> would greatly appreciate any assistance.
> >>>
> >>> Here is an example of my need:
> >>>
> >>> I would like a function to read (if column "a" = 100 and
> >>> column "b" = "manager" then sum these fields).
> >>>
> >>> I know there must be a solution but it escapes me.
> >>>
> >>> Thanks!
> >>>
> >>> Dave
> >> .
>
>

In my spread I thought I'd found a way to sumif a moving range. However, as a check I tried one set of data with different criteria and the #'s were off. The formula I'm using:

=SUMIF(H$4:H$55539,OFFSET('MassHub Fwds'!C$4,MATCH($K4,'MassHub Fwds'!C$5:C$70,0),1),OFFSET(G$3,MATCH(K4,A$4:A$55539,0),0):OFFSET(G$3,MATCH(DATE(YEAR(K4),MONTH(K4)+ 1,DAY(K4)),A$4:A$55539,0)-1,0))

The criterion for the SumIf (first offset) are basically prices on another sheet which seems to work fine, I've tested this without the sumif and does what it's told. However, when I try to define the [sum range] with offsets, the #'s are thrown off.

What the 'match' in the sum range offsets are looking at are dates. To paint the picture:

Date; Hour; SumRange; Range; Criteria; Output
9/1/06; 1; 111.50; 61.65; 9/1/06; ...
9/1/06; 2; 90.65; 60.22;
9/1/06; 3; 101; 61.65;
.
.
.
10/1/06; 1; 60.35; 61.65

Basically what's happening is the 'Range' is full of values from another spread (this is what the first offset looks up). The sum range offsets look up the date (the 'Criteria' for my output) for which to sum. One underlying problem I feel is that the 'Range' has multiple values which are the same that occur on different dates; so a SumIf without a 'begin date' and 'end date' will sum up values which are not within the criteria.

Any ideas?

I've got C++ background and I'm trying to get proficient with excel. I'm about to just breakdown and make my own functions that do what I want, but I'd rather learn excel for compatibility. Here is my dilemma:

So I'm doing this formula from sheet1. What I really would like to do in an ideal world, is instead of using apples for the
search on sheet2, I'd like to use a cell from sheet1 with that apples value in it, say A1. So for example:

A1=apples
A2=oranges

I want to search sheet two within the range of B51:B78 for any cell that contains the text apples OR oranges. IF the cell contains apples OR oranges or both, I want the cell to be part of the SUM.

If I enter A1 instead of apples in the sumif function, looking for any cell that has the apples text within it it ( If the cell has "oranges apples fruit" i still want it to return true), I get a value error because it can't look for any text "*A1*" yet it can do the same "*apples*".

If i had a simple for loop like in c++ I'd be able to do everything I wanted here in the matter of seconds. Any hints?

Also it seems one of the big limitations of the IF type functions in excel is that you can't iterate through them. So what if you want to do something like:

For all values in C1:C50, If("*apples*",1,0). That would not work and give a #VALUE error because the IF statement doesn't accept cell ranges, merely 1 cell.

Hello,

I would like to use the SUMIFS function to sum the values of multiple cells that meet one criteria that falls within a Named Range I have already established on the worksheet.

Ex: Named Range "Income" contains text B01, B02, B03...B16. If any one of those text strings are present I would like the corresponding value returned for it.

My current formula is:

=SUMIFS(Sheet1!I:I,Sheet1!A:A,Income)

Sheet1!I:I is the value to be returned
Sheet1!A:A has the text strings B01, B02, B03, etc.

***There are additional criteria conditions to be met in the formula (which is why I am using SUMIFS rather than OR) but I have excluded them for the sake of simplicity of this question.

TIA

Bjordion

Hi all,

This is my first post here after using these forums numerous times to solves my Excel problems! This one seemed so simple at first, but has stumped everyone in my department. I attached a sheet with the problem I have, simplified so it's easier to understand.

Essentially I need to write a draggable formula for Item 1 in cell C10 that can be theoretically dragged out to the end of the worksheet and down about 400 rows that will:
1.) Check to see whether the date in C9 in within EACH range in cells C1:C2, D1:D2, E1:E2, etc out to the end of the sheet, then
2.) Sum the corresponding quantities in row 3 (for Item 1. Row 4 for Item 2, Row 5 for Item 3, etc down to about 400 different items)
In theory it sounds simple and if a standard SUMIF would work, I'd have used it. The problem is that each range of dates is different, some overlap, some do not.

To make this clearer, the formula would return the value "6" in cell F10 since the date "1/16/2010" falls within the ranges D1:D2 and E1:E2 (summing the corresponding values 2 and 4 respectively).date%20range%20example.xls

Okay... I've got a tough one for you All-Stars out there. I searched the board but couldn't come up with anything quite like my current obstacle...

IN SUMMARY: Has anyone figured out a way to do CONDITIONAL AVERAGING, with a dataset that could include NULL VALUES, based on MULTIPLE criteria similar to how one would use SUMPRODUCT?

What I'm shooting for is the AVG SCORE in the Summary Section to properly calculate the average score based on mutiple criteria (REP name and DATE RANGE) and the raw data in my raw data section (which can contain null values). In this case, John should evaluate to "7.0" and Jack should come out to be "3.0"

I found these two formulas on the board but they didn't seem to fit my needs:
=AVERAGE(IF(A2:A4000="March",IF(E2:E4000="JF",IF(ISNUMBER(P2:P4000),P2:P4000))))
=SUMIF(C3:C150,K30,D3:D150)/COUNTIF(C3:C150,K30)

What follows is a simplified example but you get the point:

******** ******************** ************************************************************************>Microsoft Excel - Ex of Conditional Averaging.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG8=
ABCDEFGH1Raw*Data*Section:*******2DATEREPSCORE**Select*Date*Range:2/1/063/31/0631/5/2006John3.0*****41/26/2006Jane1.0*****52/15/2006Jack2.0*****62/19/2006John7.0**Summary*Section:**73/2/2006Jack4.0**REPAVG*SCORE*83/5/2006John***Jack0.0*93/13/2006Jack***Jane**103/27/2006Jane5.0**John**Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

In Microsoft Excel, when you use the logical functions AND and/or OR inside a SUM+IF statement to test a range for more than one condition, it may not work as expected. A nested IF statement provides this functionality; however, this article discusses...
XL: How to Use a Logical AND or OR in a SUM+IF Statement in Excel

You can use the SUMIF() worksheet function in Microsoft Excel for either of the following situations: The criteria that you can use with the SUMIF() worksheet function is limited to text, numbers, or a range, and the function cannot use array...
XL: When to Use a SUM(IF()) Array Formula

This article offers several examples that use the INDEX and MATCH worksheet functions in Microsoft Excel to find a value based upon multiple criteria.
XL: How to Use INDEX and MATCH Worksheet Functions with Multiple Criteria

I’m trying to understand how the various sumif and sumproduct functions
work, because I want to sum up data based format of the cell containing
the data.

Please note that I'm using EXCEL97 with all of the latest Microsoft
patches.

If seen the following formula used to sum the values in column “B” where
column “A” contains the term “income”.

=SUMPRODUCT((A2:A6="Income")*(B2:E6))

The fact that it works implies that the test
(A2:A6="Income")
returns a value of unity when “true” rather than the text “true”.
The SUMPRODUCT FUNCTION then performs the equivalent of a matrix
multiplication and returns the sum.
I find it rather strange that the test returns the value unity. HOW COME?

I’ve also observed that if I place the function formula.
=Cell(“format”,B2:E6)
any place else on the worksheet,
the SUMPRODUCT formula shown above returns “#value”.

WHY SHOULD A FORMULA IN ONE PART OF WORKSHEET IMPACT RESULTS IN ANOTHER?

I’ve also observed that if I use the formula
=Cell(“format”,B2:E6)
and then change the formatting of a cell within the range being tested,
such as B2,
the output of =Cell(“format”,B2:E6) does not change until I force a
recalculation even though the sheet is set for automatic recalc. WHY?

I have a problem that I hope someone can help with. I have a sheet that gives a total using a sumif and I want to popup a message box that contains the values of the cells that were called by the sumif.

I have the code that will list all the cells in the range but not just the ones called by the sumif.

I have attached a screenshot of the sheet and the VBA code is below.

I have looked at precedents but in my real spreadsheet the sumif criteria key is on another sheet.

In the picture attached A8 contains the value SUM=(A1:A7) but should contain =SUMIF(B1:B7,D1,A1:A7) and the msgbox should contain a list of the values picked up.

Private Function RangeToString(ByRef rngDisplay
As Range, ByVal strSeparator As String) As String

    'The string to separate elements on the message box,
    'if the range size is more than one cell
    
    Dim strMessage      As String
    Dim astrMessage()   As String
    
    Dim avarRange()     As Variant
    Dim varElement      As Variant
    
    Dim i               As Long
    
    'If the range is only one cell, we will return that that
    If rngDisplay.Cells.Count = 1 Then

        strMessage = rngDisplay.Value
    
    'Else the range is multiple cells, so we need to concatenate their values
    Else
    
        'Assign range to a variant array
        avarRange = rngDisplay
        
        'Loop through each element to build a one-dimensional array of the range
        For Each varElement In avarRange
        
            ReDim Preserve astrMessage(i)
            astrMessage(i) = CStr(varElement)
            i = 1 + i
            
        Next varElement
        
        'Build the string to return
        strMessage = Join(astrMessage, strSeparator)
        
    End If
    
    RangeToString = strMessage
    
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address(True, True, xlA1) = "$A$8" Then
    MsgBox RangeToString(Sheet2.Range("A1:A7"), vbCrLf), , "Items"
    End If
End Sub


I've got a problem that I've been trying to solve for most of the day. Let me try to lay it out for you.

If have my data arranged horizontally for ease of entry and I would like to sum certain cells if certain conditions are met.

DATA ARRANGEMENT
Column B: Customer Name
Column C: PO#
Column D: PO Date
Column E: Type
Column F: Total Quantity
Column G: Ship Date
Column H: Quantity to Ship
Column I: Ship Date
Column J: Quantity to Ship
Column K: Ship Date
Column L: Quantity to Ship
Column M: Ship Date
Column N: Quantity to Ship
Column O: Ship Date
Column P: Quantity to Ship
Column Q: Ship Date
Column R: Quantity to Ship

Columns G through R reflect the fact that multiple shipments are often made on one purchase order.

I would like to sum the values in columns H, J, L, N, P, R based on whether the Customer Name and Type in the row match the same values on another worksheet and based on whether the date to the left of Quantity falls within a given month.

The data on my reporting spreadsheet is arranged as follows:

Column A: Customer Name
Column D: Quantity (where the sum would be reported)
Cell A2: Type

I would like to end up with the quantity to be shipped in a given month for a given customer and type.

The thing that seems to be giving me the biggest problem is that one of the criteria is referring to a value that to the left of the numbers that I want to sum and therefore part of the Sum Range.

Please help!!!

Thanks!


No luck finding an answer? You could always try Google.