I have trouble with SUMIFS equation and I hope if someone could help me and the problem in this code
Code:
=SUMIFS(E2:E5,B2:B5,">B2",B2:B5,"<A2")
The formula doesn't give me the true value
Note : Cell (B2) and (A2) are not fixed they are variable
How to fix this problem ?
Howdy All!
I've been working on a project in Access. It works fine, and exports tables to Excel. The number of records varies each time the Access file exports. Therefore, I've created a chart w/ dynamic ranges.
The chart works fine....It grows and shrinks accordingly. I'd like to include a line to show the average...dynamically. I found an equation that works w/ dynamic ranges. This function works well (found in cell C2).
However, I can't get that info turned into a line on my column chart. When I add that value into the chart, and change it to a line chart, I end up w/ one single data point. Understandably, because that's all there is.
Therein lies the question: How do I apply that value dynamically for each record so it shows as a line on my chart?
I've tried using trend lines, but they don't actually average...so they're not very helpful.
Attached is an example. Feel free to play w/ the data (add or remove as you will). The column chart changes accordingly, as does the average value in C2. But...I'd still like to get the average value to show as a line...
Thanks for the help.
I'm trying to use this new worksheet function, using the example provided
in the online help, but it doesn't work asbsolutely... The only way I
succeded was using it like a regular SUMIF...
I think it should be some bug in this function...
--
Ciao
Franz Verga from Italy
Hi,
I am using a Sum Ifs Function to report 1 for true and 0 for false, my equation looks for a word and if it has a 1 value in the matching column. This is as follows:
=SUMIFS('Total Trigger Tracker'!AG:AG,'Total Trigger Tracker'!C:C,C30)
Where AG:AG is where the value is
C:C is the column where the name is stored
C30 is the actual name it is looking for in C:C
I need to send this to someone that only has excel 2001 and Sum Ifs does not work. Is there an alternative? I don't want to sum any answers, merely want to report a 1 for true or 0 for false.
Many many thanks
excel doesn't seem to allow multiple criteria on the same column with sumifs function. For e.g:- i was trying to sum values in Col "Money Earned", based on two criteria such as "money earned thru' "offline" as well as "online" marketing under the column "source"
any alternatives...
Hi, I have a dynamic named range with
=INDIRECT('Chart Data'!$D$2):INDIRECT('Chart Data'!$D$3)Where the cells D2 and D3 contain a cell reference in each (which is dependant on a date which changes).
When adding into a chart it does not work. I understand this is because you cannot use the indirect () function for dynamic named range charts. Is there a better way to do this using OFFSET?
Thanks
Hi,
Please see attached spreadsheet.
I have a table with same layout (number of columns / position of columns) on each worksheet and each worksheet represents a different quarter.
I'd like to extract comparative data for each quarter to different cells per Sheet 7 based on a set of predefined criteria.
Currently I am able to use the Sumifs formulae but I have to keep changing the formula to look at different worksheet names.
I'd like the Sumifs formula to know that inter alia in:
* Cell B4 I want to extract the data from worksheet Q1 2009.
* Cell C4 I want to extract the data from worksheet Q2 2009.
* Cell B5 I want to extract the data from worksheet Q1 2010.
Perhaps Sumifs formula is not the best answer.
I hope that I have explained myself correctly and that the attached worksheet helps to explain what I'm looking for.
Many thanks,
Rob
I have created dynamic named ranges using cell reference and index formula. e.g. $a$1:index($a$1:$A$300,counta($a$1:$A$300))
I used those dynamic named ranges in sumifs and sumproduct (because in sumifs it didnt work, I tried sumproduct also) like
=SUMIFS(ORDERS,PRODUCTS,PRODUCT1,DATES,">="&DATE1,DATES,"
Hi all
after trying to do this by my self with some online research i almost got it but it isn't quite there, i am trying to use the bellow data base in a chart with a drop down tool box.
the data (example): see the attached sheet.
i want to be able to choose a currency (tenor) from a drop down menu and to have the data series in a line chart.
i want the graph to change when i toggle between the currency pairs.
i have some knowledge of offset functions and name defining a range but i couldn't make it work.
is it a problem to have the data series in a line instead of a column?
Thanks.
