[Solved] Conditional sum array syntax

Discuss the spreadsheet application
Post Reply
Aduxas
Posts: 20
Joined: Wed Feb 07, 2018 3:48 pm

[Solved] Conditional sum array syntax

Post by Aduxas »

Column A of my sheet contains names. In columns G:AK, I have a daily value, blank if undefined. This block is repeated vertically (different months). The line range of all blocks is 1:280. In a different sheet, I have a list of unique names. For each name, I would like to count how many daily values exceed a certain (constant) minimum value in the first sheet. The match has to occur on the name, as blocks are not sorted,name subsets differ in each block, and summary lines are inserted at the top and bottom of each block. Thanks for any hints.
Last edited by Aduxas on Tue Aug 13, 2019 7:24 pm, edited 1 time in total.
OpenOffice 4.1.4 on Kubuntu 14.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: conditional sum array syntax

Post by Villeroy »

Your layout is insane. You never get any information from this.
Attachments
t98915.ods
Another normalized table and pivot table
(63.84 KiB) Downloaded 99 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: conditional sum array syntax

Post by MrProgrammer »

Aduxas wrote:Column A of my sheet contains names. In columns G:AK, I have a daily value, blank if undefined. This block is repeated vertically (different months). The line range of all blocks is 1:280. In a different sheet, I have a list of unique names. For each name, I would like to count how many daily values exceed a certain (constant) minimum value in the first sheet. The match has to occur on the name, as blocks are not sorted,name subsets differ in each block, and summary lines are inserted at the top and bottom of each block. Thanks for any hints.
You'll probably use SUMPROUCT.
[Tutorial] The SUMPRODUCT function

For futher assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). You must provide some examples of the correct counts for a specified minimum value so that a proposed formula can be compared with them. Your "summary lines" may complicate the formula. Presumably your attachment will show what "name subsets" are. Be sure to explain fully unless you're certain this will be obvious.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Aduxas
Posts: 20
Joined: Wed Feb 07, 2018 3:48 pm

Re: Conditional sum array syntax

Post by Aduxas »

I'll add an example file. I had already tried SUMPRODUCT but didn't get anywhere because of the different array sizes. I don't expect the summary lines to be a problem as they are easily filtered out.
OpenOffice 4.1.4 on Kubuntu 14.04
Aduxas
Posts: 20
Joined: Wed Feb 07, 2018 3:48 pm

Re: Conditional sum array syntax

Post by Aduxas »

I have solved it. It's actually quite simple.

Code: Select all

=SUMPRODUCT(('Data'.$A$2:$A$280=A1)*('Data'.$G$2:$AK$80>100))
'Data' is the sheet holding the data. First column is the name; columns G-AK are the numbers to be matched. I picked a minimum value of 100 to be counted. A1 is the name to be matched.
OpenOffice 4.1.4 on Kubuntu 14.04
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [SOLVED] Conditional sum array syntax

Post by RusselB »

Just a quick note about your "minimum value"... your minimum value, according to your formula, would not be included.
If you want your minimum value to be included as well, change the > to =>
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply