DCOUNT In-Line Multi-Conditional

Discuss the spreadsheet application

DCOUNT In-Line Multi-Conditional

Postby JavaCofe » Mon Apr 14, 2008 6:16 pm

Hi,

I'm trying to create a table for charting purposes which has hourly numbers per data category. Not all categories contain all hours and theres currently 3 cats.

I'm currently using this: =DGET(C6:G7;N4;N5:N6) .. C6:G6 are Column Headers (DateTime|Category|Data1|Data2|Data3) N4 = "Data1" N5 = "Category" N6 = "FooCategory". This all works fine for one data row :)

I need the DGET to validate on both DateTime (increases +1hr per row in adjacent col.) and Category (per column header) but can't figure out the syntax.. this is what I've testing justnow:
=DGET(C6:G7;N4;"FooCategory"="FooCat1")

what I'm looking for is something like:
=DGET(C6:G7;N4;(AND("FooCategory"="FooCat1*";"DateTime"="$I10")))

any help would be greatly appreciated!

Thanks
-Scott
JavaCofe
 
Posts: 7
Joined: Tue Apr 01, 2008 12:01 pm

Re: DCOUNT In-Line Multi-Conditional

Postby Villeroy » Mon Apr 14, 2008 7:14 pm

what I'm looking for is something like:
=DGET(C6:G7;N4;(AND("FooCategory"="FooCat1*";"DateTime"="$I10")))

Criteria range:
Code: Select all   Expand viewCollapse view
FooCategory DateTime
FooCat1     2008-04-14 18:00 [=$I10]

Mind also Tools>Options...Calc>Calculation:[X]"Enable regular expressions in formulas"
FooCat1 means: contains "foocat1" (not case-sensitively) anywhere.
^FooCat1 means: starts with "foocat1"
^FooCat1.+ means: starts with "foocat1" followed by any other char (the dot), at least one of the preceeding any char (quantifier "+").
http://wiki.services.openoffice.org/wik ... _in_Writer
http://wiki.services.openoffice.org/wik ... ns_in_Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DCOUNT In-Line Multi-Conditional

Postby JavaCofe » Mon Apr 14, 2008 8:51 pm

thanks Villeroy,

my problem isn't (although admitidly it looks stupid now) the regex expression itself, more how I get it to check two database criteria, one of being the DateTime which is on the same row as the forumlae.
JavaCofe
 
Posts: 7
Joined: Tue Apr 01, 2008 12:01 pm

Re: DCOUNT In-Line Multi-Conditional

Postby Villeroy » Mon Apr 14, 2008 9:04 pm

No, this is what you can not do with D-fucntions since each criteria range has 2 rows at least. A separate data pilot (aka pivot table) can do this.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28667
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests