Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 03 Jun 2020 16:09:49 +0200I want to add a Data-Validity cell as a criterion to a SUMIFS statementhttps://ask.libreoffice.org/en/question/247888/i-want-to-add-a-data-validity-cell-as-a-criterion-to-a-sumifs-statement/ [C:\fakepath\Guided Project_Shouvik.xlsx](/upfiles/15911926975137513.xlsx)
Please see the above document --- "Sheet named- Task 7 &8 Solution Example".
The cell B1 in the above sheet has a drop-down menu created using Data Validation in Google Sheets.
When we change the Drop-down menu option ( January 2018, February 2018,etc.) the values in the sheets change according to the month selected.
I am trying to recreate the same picture Using LibreOffice Calc.
The Formula I have used in "Task 7a Analyse your expenses" :cell-B7 is =SUMIFS($'Task 7b Expense data'.E2:E101,$'Task 7b Expense data'.B2:B101,$B$1,$'Task 7b Expense data'.D2:D101,$A7)
That is --- I am trying to sum all expenses based on month and year which is input from the "***Data-Validity Cell B1***" and also based on the category of payments based on cell A7 (Paycheck, Mortgage, Home Improvements,etc.).
How can I do this ?
(By the way I already know how to do this on Google Sheets but I want to use it on Calc.)shouvikdasWed, 03 Jun 2020 16:09:49 +0200https://ask.libreoffice.org/en/question/247888/Sum if multiple criteriahttps://ask.libreoffice.org/en/question/247193/sum-if-multiple-criteria/ Is there a way to construct a formula to produce a sum based on several criteria?
For example, I'd like to get a sum in L3 of column F where it's today's date (column A), and the symbol in column B = K3.
![image description](/upfiles/15907739231128919.png)
I tried several things with SUMIF, but it's beyond my meager abilities.
Many thanks in advance for any ideas.
DMDougKMilesFri, 29 May 2020 19:44:00 +0200https://ask.libreoffice.org/en/question/247193/SUMIFS using array as criteriahttps://ask.libreoffice.org/en/question/227052/sumifs-using-array-as-criteria/I want to sum column 14 where column 2 is equal to a specific value and column 10 either of 3 values. I tried the one similar to what I did in MS Excel but it's not working. I also tried the one specified in this link
https://ask.libreoffice.org/en/question/158555/sumif-using-multiple-partial-matches/
but still failes. It fails to the 2nd criteria.
These are the samples I tried:
=SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), {"Unpaid", "Partial", "Bad"})
=SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), "Unpaid|Partial|Bad")
=SUMIFS(INDEX(Deliveries,,14),INDEX(Deliveries,,2),"0043", Index(Deliveries,,10), "*Unpaid*|*Partial*|*Bad*")
Please help, thanks!HateemWed, 29 Jan 2020 12:37:15 +0100https://ask.libreoffice.org/en/question/227052/Calc: How to "OR" conditions in SUMIFS?https://ask.libreoffice.org/en/question/223334/calc-how-to-or-conditions-in-sumifs/ Is there a way to `OR` the conditions in this `SUMIFS` expression:
SUMIFS=(A1:A10, B1:B10, "*foo*", B1:B10, "*bar*")BeOSWed, 01 Jan 2020 09:32:10 +0100https://ask.libreoffice.org/en/question/223334/Calc: How to AND/OR several text patterns?https://ask.libreoffice.org/en/question/223333/calc-how-to-andor-several-text-patterns/Hi,
How can I AND (or OR) several text patterns in a `SUMIFS`?
In pseudocode:
SUMIFS(A1:A100, B1:B100, "*FOO*" | "*BAR*" | "*BAZ*")BeOSWed, 01 Jan 2020 09:23:40 +0100https://ask.libreoffice.org/en/question/223333/How to use WildCard as part of SUMIFShttps://ask.libreoffice.org/en/question/195295/how-to-use-wildcard-as-part-of-sumifs/ I'm trying to calculate the totals based upon three categories, which may or may not all be filled. In excel I can just place an asterisk in the blank Categories as per the DATA section below. But it doesn't seem to work in LO, any ideas what I can do?
SUMMARY
The following starts at A5
CAT A | CAT B | CAT C |
6 > HOUSEHOLD | UTILITIES | ELECTRIC | =SUMIFS($P$6:$P$22,$M$6:$M$22,A6,$N$6:$N$22,B6,$O$6:$O$22,C6)
7 > HOUSEHOLD | UTILITIES | GAS |
8 > HOUSEHOLD | GROCERIES | |
DATA
The following starts at M5
CAT A | CAT B | CAT C | VALUE
6 > HOUSEHOLD | UTILITIES | ELECTRIC | 50
7 > HOUSEHOLD | UTILITIES | GAS | 35
8 > HOUSEHOLD | GROCERIES |*| 18
9 > HOUSEHOLD | GROCERIES |*| 10aSystemOverloadMon, 27 May 2019 16:02:26 +0200https://ask.libreoffice.org/en/question/195295/Whats wroing with Sumifs formula.https://ask.libreoffice.org/en/question/190979/whats-wroing-with-sumifs-formula/ Hi, whats wrong with this fromula. Correct result should be 30.8% but it returns 0%
=SUMIFS($Q$5:$Q$25,$P$5:$P$25,">=4",$P$5:$P$25,"<=6")
Thank youSumsFri, 19 Apr 2019 12:35:15 +0200https://ask.libreoffice.org/en/question/190979/Sumif using multiple partial matcheshttps://ask.libreoffice.org/en/question/158555/sumif-using-multiple-partial-matches/Hi All,
Coming from a low-medium skill level with Excel, trying to get my head around the differences with LibreOffice Version: 6.0.4.2 (please be gentle).
Likely a better way of doing this, however I have been maintaining a basic income/expense spreadsheet where I take ledger style dumps from multiple sources and then use an array containing key words/phrases to categorize entries. Structure of the formula in Excel is as follows;
=SUM(SUMIFS(sum_range,criteria_range,{"*Transport*","*Courier*"}))
Essentially any entry with a description containing Transport of Courier would have the dollar amount of that entry added to the Transit costs category in my main sheet.
Have tried this in LibreOffice and I cannot seem to get it to work. I read something about Arrays not being able to be nested. If this is the case, what would be the best way to sum the numbers in one column when a partial match on multiple criteria is found in another column?
Really appreciate the assistance.brashquidoThu, 21 Jun 2018 07:00:34 +0200https://ask.libreoffice.org/en/question/158555/How to make a sum in B2 of all cells in B where the corresponding A contains A2 and D is 1.https://ask.libreoffice.org/en/question/144114/how-to-make-a-sum-in-b2-of-all-cells-in-b-where-the-corresponding-a-contains-a2-and-d-is-1/Hi
I would be so happy to get this complete formula. Please use the example file.
Klaus
[C:\fakepath\Example sumifs.ods](/upfiles/15169139955168667.ods)inJesusThu, 25 Jan 2018 22:00:19 +0100https://ask.libreoffice.org/en/question/144114/Sum up based on date in the same rowhttps://ask.libreoffice.org/en/question/127492/sum-up-based-on-date-in-the-same-row/Hi there,
I'd like to sum up a column if and only if other column say so. I thought it could be done with SUMIF. My problem is that I have a sheet with dates on column A and values on column B and I am trying to do weekday report of progress...
My first try was something like this: "=SUMIF(B1:B100,WEEKDAY(A1)=3)". It is obivious wrong because it sums up all elements or it does nothing. I'd like when I try to sum up B2 I test B1 and not A1. Sum up B3, test A3 and so on.rrluccaMon, 28 Aug 2017 13:00:20 +0200https://ask.libreoffice.org/en/question/127492/How to sum by year and month from external sourcehttps://ask.libreoffice.org/en/question/118379/how-to-sum-by-year-and-month-from-external-source/I have one file where I have consolidated by year and month like this
**Local file in work**
YEAR MONTH SUM_PRICE
2013 1
2013 2
2013 3
2013 4
2013 5
While in another file I have the details like this
**External file is Sales.ods**
DATE YEAR(fx) MONTH(fx) PRICE
2013-01-15 2013 1 3.5
2013-01-20 2013 1 4.5
2013-01-22 2013 1 6.5
2013-01-23 2013 1 1.5
2013-02-15 2013 2 2.3
2013-02-20 2013 2 3.4
2013-03-11 2013 3 234.5
2013-04-05 2013 4 35.0
2013-05-14 2013 5 3.99
2013-05-23 2013 5 3.5
I want to get the sum per year and month, but I don't know how to perform this
I have been trying this formula in my local file to get sum from external file, where A is the date and D is the price
> =IF(AND(YEAR('file:///D:/files/Sales.ods'#$Sales.$A$2:$A$103) = A3,MONTH('file:///D:/files/Sales.ods'#$Sales.$A$2:$A$103) =B3),'file:///D:/files/Sales.ods'#$Sales.$D$2:$D$103,0)
I just get the first value that matches then the rest are zeros.
Then I tried (Remember D is the price from the detailed external file, B is the extracted year, C is the extracted month A2 and B2 are the current cells from local file)
>=SUMIFS('file:///D:/files/Sales.ods'#$Sales.$D$2:$D$103, 'file:///D:/files/Sales.ods'#$Sales.$B$2:$B$103, A2, 'file:///D:/files/Sales.ods'#$Sales.$C$2:$C$103, B2)
With this I get a Err504
Can this be achieved with another function or am I closer with my functions?MaximusDecimusThu, 03 Aug 2017 05:14:50 +0200https://ask.libreoffice.org/en/question/118379/Having a cell reference for SUMIFS that should work as a wildcardhttps://ask.libreoffice.org/en/question/110115/having-a-cell-reference-for-sumifs-that-should-work-as-a-wildcard/Hi!
I have a formula that looks up sales-data. We sort everything in categories (1-5) and then groups (01-99) and then models (01-99) and I use a SUMIFS to sort the data. Today my formula looks like this if I want to get info from every model in category 4 group 87:
=SUMIFS($'137'.$G$2:$G$100000;$'137'.$D$2:$D$100000;$A5;$'137'.$M$2:$M$100000;"=487.*")
I would like to change the last part to reference to a cell instead so I could change search criteria quick and easy, and it works if I insert the full CGM type in the reference cell (48710 for example if I want to look up that specific model) but if I want everything in 487 the wildcard doesn't work. I tried .* in the cell and .* in the formula, like this:
=SUMIFS($'137'.$G$2:$G$100000;$'137'.$D$2:$D$100000;$A5;$'137'.$M$2:$M$100000;"=$E$2.*")helgesson88Tue, 11 Jul 2017 23:06:06 +0200https://ask.libreoffice.org/en/question/110115/Sum values in one column based on criteria in anotherhttps://ask.libreoffice.org/en/question/97204/sum-values-in-one-column-based-on-criteria-in-another/ I have a spreadsheet with column A (A8:A30) containing names and column D (D8:D30) containing points. Column E (E8:E30) is annotated with a sequence number when each name is contacted. Not all names will be contacted in a given session. I want the formula to determine if a contact number was placed in column E, then sum the points for each name contacted in cell D32. I also need the total number of contacts (not the sum of the sequence numbers) from column E(E8:E30) counted into cell D33. The end result will give the total number of contacts and the points earned for that session.barnmichaelSun, 28 May 2017 15:28:27 +0200https://ask.libreoffice.org/en/question/97204/Libreoffice Calc Sum of values depending of month and year of a range of dateshttps://ask.libreoffice.org/en/question/94198/libreoffice-calc-sum-of-values-depending-of-month-and-year-of-a-range-of-dates/ Hello
I'm quite new to Libreoffice and Spreadscheetprograms generally, please keep it in mind.
I have a very large spreadsheet with following data
- Column C - Date in form of DD.MM.YYYY range form 2013 to 2017
- Column E - Numbers (Positive and negative values)
I need to get added all values in column E with following criteria
- for a month (for example 05 - May)
- of a year (for example 2015)
- which are either positive or negative (I will have two formulas, one
for the sum of all positive numbers and another for the negatives)
I hope I explained it correctly.
Thanks in advanceAutistaThu, 04 May 2017 21:21:57 +0200https://ask.libreoffice.org/en/question/94198/Why doesn't SUMIF take cell data?https://ask.libreoffice.org/en/question/80630/why-doesnt-sumif-take-cell-data/=SUMIFS('Tailoring Mats'.G:G, 'Tailoring Mats'.I:I,">K20")
Returns 0 when K20 has 375 in it, but
=SUMIFS('Tailoring Mats'.G:G, 'Tailoring Mats'.I:I,">375")
Returns the correct number.
I've tried this on a bunch of other rows and it appears that putting another cell in for the test always returns 0.MrAdventureSat, 29 Oct 2016 20:31:54 +0200https://ask.libreoffice.org/en/question/80630/will sumifs work across workbooks?https://ask.libreoffice.org/en/question/61352/will-sumifs-work-across-workbooks/ I am trying to produce a monthly report sheet in a separate workbook to the accounts and stock sheets so need to be able to for example show the value of all stock sold in November - I had the formulas working within the same workbook as the data but i cant get it working across different workbooks.
=SUMIFS('file:///C:/Users/Antiques and Vintage/Dropbox/Antiques and Vintage/2015-16/G A&V 2015-16 Stock.xls'#$'Stock BF'.F4:F544,'file:///C:/Users/Antiques and Vintage/Dropbox/Antiques and Vintage/2015-16/G A&V 2015-16 Stock.xls'#$'Stock BF'.J4:J544,"4/15")
Produces a Err:504
Many thanks
AdamrockorangFri, 27 Nov 2015 14:26:58 +0100https://ask.libreoffice.org/en/question/61352/LibreOffice Calc -- SUMIFS with Regular Expressionhttps://ask.libreoffice.org/en/question/46922/libreoffice-calc-sumifs-with-regular-expression/I have a spreadsheet where I'd like to calculate the cumulative count of some quantity in column A where several conditions hold on columns B, C, ... One such condition is a check on the values of column X to see whether they begin with a pattern "PAT" or not. I tried the following but it did not work a̶s̶ ̶S̶U̶M̶I̶F̶S̶ ̶s̶e̶e̶m̶i̶n̶l̶y̶ ̶d̶o̶e̶s̶ ̶n̶o̶t̶ ̶y̶e̶t̶ ̶s̶u̶p̶p̶o̶r̶t̶ ̶r̶e̶g̶u̶l̶a̶r̶ ̶e̶x̶p̶r̶e̶s̶s̶i̶o̶n̶s̶:
SUMIFS($A$1:$A$100,$B$1:$B$100,$Z$1,$C$1:$C$100,">="&POWER($Z$2,$Z$3),$X$1:$X$100,"=PAT.*")
Is there any way of doing this in LibreOffice without defining and using auxiliary columns?aldabFri, 27 Feb 2015 22:07:14 +0100https://ask.libreoffice.org/en/question/46922/Does SUMIFS work in the latest version?https://ask.libreoffice.org/en/question/35241/does-sumifs-work-in-the-latest-version/I LOVE calc, and hate Excel. However I have a project now that needs to use SUMIFS and COUNTIFS. The following formula works perfect in Excel, but gives the result of "0" in Calc. (I have formula syntax set to Excel A1 for this formula, but I have also tried it in Calc A1)
=SUMIFS(I:I,D:D,"2014-04*",F:F,"2014-05*",C:C,{"paid","partially*"})
I cant get it to work at all. Is it because SUMIFS does not work, or am I doing something wrong? Whats odd to me is that SUMIFS shows as a formula option in Calc, but doesnt come up in Calc wikis, or even that often in discussions. Thanks guys!digipro00Mon, 09 Jun 2014 17:26:22 +0200https://ask.libreoffice.org/en/question/35241/Column Label in a SUMIFS function gives an Err:502https://ask.libreoffice.org/en/question/30888/column-label-in-a-sumifs-function-gives-an-err502/I am struggling with a strang Err :502. The following function
=SUMIFS('Net';'Compte';$D$2;'Rentree';"<="&F4) results in a Err:502 on one spreadsheet and not an another with column labeled exactly the same.
If I replace in the formula the column labels by actual cell range references (ie D10:D1000), the formula works fine.
Any idea where it might comme from?yvnicolasSat, 08 Mar 2014 06:42:03 +0100https://ask.libreoffice.org/en/question/30888/Sumifs alternativehttps://ask.libreoffice.org/en/question/17738/sumifs-alternative/I knew that LibreOffice not yet support the Excel '*IFS' (sumifs, averageifs, countifs, etc) functions.
Is there any alternative formula for conditional sum with more than one criteria?PaijoMon, 20 May 2013 10:26:28 +0200https://ask.libreoffice.org/en/question/17738/