Free Microsoft Excel 2013 Quick Reference

Using SUMIF with YEAR function in Criteria

I have a worksheet that contains gas usage for my vehicle.

Column A = Date
Column B = Odometer
Column C = Cost
Rows 2 thru 250 are used.

I would like to summarize the Cost data for each year, 2006 thru 2011.

How do I utilize the YEAR() function in the criteria to limit the data summed to a specific year?

Thank you!


Post your answer or comment

comments powered by Disqus
hi

can i sumup a full column list using "sumif", with more than one
criteria, all based on a single coloum? for example i am having data in
A1:C4

60 80 70
50 30 40
20 40 70
100 60 80

what i want is the sum C1:C4 if either "A1>=50 *or* A4

Hi guys,
I need to use SUMIF with more than one criteria, and I'm having problems. I'm not an expert with Excel formulas, and although I found a site that (supposedly) explained how to get round this problem (using DSUM), I couldn't make sense of it. I'll try to explain my situation in plain English...

My spreadsheet contains 2 ranges of data (one from System X & one from System Y) to be reconciled.

Column A contains the currency of the deal from System X.
Column B contains the type of deal from System X.
Column C contains the amount of the deal from System X

Column E contains the currency of the deal from System Y.
Column F contains the type of deal from System Y.
Column G contains the amount of the deal from System Y.

Now, what I need is for Column D to Sum all amounts from Column G where the deal type in Column F is equal to the deal type in Column B AND the currency in Column E is equal to the currency in Column A.

I hope this makes sense, as I can barely get my head round it myself.

If anyone knows the solution, your advice would be greatly appreciated.

Many thanks,
Jon

I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:

Filter:

does not begin with - 3

and / or

does not begin with - 9.

I want to add a third 'and' criteria .. is it possible, and if so, how?

The column that I am trying to filter has numbers formatted as text.

hi

can i sumup a full column list using "sumif", with more than one criteria, all based on a single coloum? for example i am having data in A1:C4

60 80 70
50 30 40
20 40 70
100 60 80

what i want is the sum C1:C4 if either "A1>=50 or A4<100", likewise "A2=50 and A4=100"..and so on!

help pl?

-via135

SUMIF and SUMPRODUCT Functions in Calc - NeoWiki

The SUMIF function belongs to the mathematical functions category and the SUMPRODUCT function to the array functions one. They allow you to work with cell ranges which meet some ...

Hello,

I am relatively new to this forum. Please accept my apologies if i had missed out on any forum rules. Now..

1. i have a vba code which uses evaluate with Sumproduct function and it is (rather...was!) working
2. i tried to expand this by including a date comparison but it is not working
3. Date is stored in a cell which is in turn stored in a variable and i used that variable in the function
4. Xmonth and Xweek (mentined below in code ) are names defined. we can assume Xdate also as same.
5. Val1 / Val2 is declared as variant and it has values from cell and val3 stores date.

Relevant extract from the code
val1=range("i3").value
val2=range("i4").value
val3=range("i5").value

myval=evaluate("=sumproduct((xmonth=""" & val1 &""")*(xweek=""" & val2 &""")

but..if i append this with the following code it doesn't work...i tried multiple options like clng,datevalue etc..it give #name,#value and error message at times.

myval=evaluate("=sumproduct((xmonth=""" & val1 &""")*(xweek=""" & val2 &""")*(xdate=""" & val3 &""")

can someone help me out on this..and please revert for any additional information which is required which can help me in getting a resolution. Thx.

Hi,

I want to Transpond and fast link cells with drag function in bottom right corner of an cell.

Example:
Stand in cell A1, enter "=C1"uses the "drag link cell" in the down right corner of a cell. I will automaticly link
A1 - C1
A2 - C2
A3 - C3 ect

My question is if it is possible to drag horisonal and link vertically.

A1 - C1
A2 - D1
A3 - E1

Are the any button command that i can ues/press so this is done automatically or an easy funtion?

Appreciate your help!

I am trying to write a report using the COUNTIF function but in order to accomplish what the function needs to do, I need to use it with another function. Here is what I am trying to do:

I have a worksheet that is my data table and a worksheet that is my report.
I have a attached a visual to make this more clear. I am trying to count in the report the number of occurrences in the data table for 1A, 1B, etc for all rows that have Region # 0691 as 0691 is the region # for Chicago. There is more than 1 "region" that I need to do this for. I tried to use an IF statement and then embed a COUNTIF statement into that but it did not work as it returned a count of all cells that had 1A, not just the ones with Region # 0691. If anyone has any suggestions please let me know. I am trying to do this without using hidden cells or adding more worksheets. Thanks in advance for the help.

hi,

i always thought is was impossible to change a cell with a function in another cell.
however i saw the function bdh of the bloomberg addin able to do this.
so in cell a1 you type bdh(...) and executing this function gives results in range a1:b252.

how can you do it?

Is it possible to use excel's QUARTILE function in an array formula?
I want to calculate the 2nd & 3rd quartiles of a column of data, but only where the value in another column ="Y".

Simon

When using the paste link function in Excel, if the destination worksheet is
updated and a row is inserted in between data, will that row be automatically
inserted in the source file?

Hi,

I am using the SUMIFS function in the following formula and it works with no problems:

=SUMIFS(C1:C10, A1:A10,">=1/5/8",A1:A10,"=D1",A1:A10,"=(D1)",A1:A10,"

Hi,

I am trying to use the SUMIF function in conjunction with the date function YEAR, but am continually getting an error.

I am attempting somthing like the following:

where column A contains daily dates, column E has data for each day, and H3 has the year I wish to sum for (ie. 1982).

Any ideas?
Thanks
HT

I want to perform a SUMIF function, but the criteria anchoring the SUMIF is embedded in the middle of a cell.

I'm attaching a sample of the data. I want to SUMIF the vendor names listed in rows 26 to 32 using the values in column G, and find the embedded vendor name in column A. I think you'd use a MID function to find the vendor names, but I'm not sure.

Thanks!

Hi There,

I tried built in sumif but no use

I have a sheet where i need sum if result with multiple ranges & criteria
See attached sample sheet

coumn C & colum E is range to search in
criteria is April & Airfreight

Result cell (L3) to show total sum for April but only for Airfreight

Thanks in advance for your prompt help

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Can I use the SUMIF or another function to look in multiple columns for multiple criteria, and then sum the totals in another column once they meet all of the criteria?

Hello,

I have a set of data that has a label in column A the sales in column
B, C & D:

Column A Column B Column C
Joel 500 677
Joel 575 752
Joel 650 827
Claudia 725 902
Tarzan 800 968

I'd like to run SUMIF where the "Range" is column A, the "Criteria" is
Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C)
I only get the sum of column B which is 1,725.

Can someone hook me up with a formula that can sum both column B & C
in this situation.

Thanks, in advance

Daniel

I want to use the sumif function to add up all expenses that occur between
two dates (Say, during the month of September, so between 09/01/2004 and
09/30/2004). I can't figure out how do use sumif with two arguement (sumif
criteria is THIS AND THIS)?

I need help with the excel timeline function, I want to use it with severla
dates in a graph.

Please Help!!!!!!!!!!!!!

This is the first time I'm trying to use a user defined function in excel. I want to use it in the columns I & J. I already have the formulas in column I & J.

In Column I :
=((H3/20)/G3)

In Column J:

=IF(A4="","",IF(SUMPRODUCT(--($A$2:$A4&$F$2:$F4=A4&F4))=1,(20-H4)/20,(20-SUMIF($F$2:$F4,F4,$H$2:$H4))/20))

Now here's the user defined function which I've got from my friend which I can use.
Public Function SplitNames(NamesCell As Range)
Dim vecNames As Variant
Dim cell As Variant
Dim yourPcge As String
Dim tmp As String

vecNames = Split(NamesCell.Value, ",")
For Each cell In vecNames

yourPcge = "xx" ' replace with code to calculate percentage
tmp = tmp & cell & " : " & yourPcge & " ; "
Next cell

SplitNames = Left$(tmp, Len(tmp) - 3)
End Function How do I use the formula I've in column I & J in this user defined function in the code-line in italics? Also, how do I use this in the excel I've created.

Hey all, first time here, interesting forums and nice format.

I had a question about the SUMIFS() function. Below is a sample data set:

Category	Year	Value
a	2008	2
a	2009	4
b	2008	6
b	2008	1
b	2009	3
c	2008	5
c	2008	7
c	2009	9
d	2008	8
If I wanted to sum all the a's for 2008 I would do...

SUMIFS(C:C,A2:A9,"a",B2:B9,"2008")

Simple enough...

But lets assume I was to sum up anything that is in "a" or "b" and in 2008...

I tried something like...

SUMIFS(C:C,A2:A9,"Or("a","b")",B2:B9,"2008")

But no success, I do not know how to make it look for multiple values within an array.

Notes:
I know I could use two different sum functions to do this, I want to avoid this since there are a lot of different categories going into my calculation.

Also, I know I could write an if statement in column D and have a value group them up and then sum off of that, I was trying to avoid that as well.

Thanks ahead of time for your help.

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I am trying to put together a report of POS data using more complex formulas. Here is a short example:

Segment Wk 1 Wk 2 Wk 3 Wk 4 Wk 5 Wk 6 Wk 7 Wk 8 Wk 9 Wk 10 Wk 11 Wk 12
Car $10,170 $11,177 $12,329 $12,890 $12,985 $14,199 $13,226 $14,707 $14,627 $14,547 $13,721 $15,957
Car $10,793 $11,663 $13,395 $13,207 $13,999 $13,648 $12,759 $14,077 $13,084 $14,849 $14,336 $15,764
Doll $625 $613 $816 $902 $1,053 $979 $970 $1,244 $1,035 $893 $776 $1,195
Doll $413 $471 $690 $798 $767 $653 $807 $825 $810 $847 $829 $939
Coloring $3,581 $3,671 $5,509 $5,390 $5,408 $5,058 $5,130 $6,007 $5,397 $5,184 $4,220 $4,848
Coloring $376 $542 $906 $1,075 $1,060 $915 $893 $859 $585 $561 $450 $474
Coloring $1,405 $1,692 $2,171 $2,485 $2,165 $1,833 $2,024 $2,419 $1,914 $1,818 $1,594 $1,872

I need to get a sum of the last 4 wks, 12 wks, and YTD of each segment. I have all the data on one sheet, and a chart on another, like this:

Latest 4 wks Latest 12 wks YTD
Cars $85,457 $335,401 $814,219
Dolls $69,492 $410,343 $1,020,006
Coloring $116,702 $399,997 $927,207

I am been just manually selecting the data using the SUM function, but I'm sure there's an easier and quicker way to do it, since I have to make this report weekly with updated data.

I've tried using =SUMIF('pos data'!B2:b8,a3,'pos data'!C2:c3)+SUMIF('pos data'!B2:b8,a3,'pos data'!D2:D3)+SUMIF('pos data'!B2:b8,a3,'pos data'!E2:e3)...and so on for 12 wks, In English, if Data Sheet column B=Cars, add C2 and C3 (wk 1), then if Data Sheet column B=Cars, add d2 and d3 (wk 2), and so on, and then add all those answers together again, which works but gets really lengthy!.

I have also tried =SUMIF('pos data'!B2:b8,a3,'pos data'!C2:N3), which will only add the first column (C2 + C3) together, not the whole 2D section.

What should I do to fix this? Maybe an array, but I can't figure out how to incorporate this.

Thanks!


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