openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Meyer <johnme...@pueblocomputing.com>
Subject Re: Indirect and Address problem (from VLOOKUP and Macros)
Date Thu, 03 Oct 2013 17:57:20 GMT
On 10/3/2013 11:42 AM, Brian Barker wrote:
> At 10:59 03/10/2013 -0600, John Meyer wrote:
>> After looking over this problem a bit, I decided to scrap the sheet 
>> name idea and just have all sales on one sheet.
>>
>> sales are in this format:
>> DummyField, Date, DummyField, DummyField, SalesRep
>>
>> For this table, I want to get the number of sales generated for a 
>> particular agent by date.
>>
>> Sales Rep                            10/1/13       10/2/13 10/3/13
>> 12345                                      ## ## ##
>> 45678                                      ## ## ##
>>
>> Would this lend itself to a DCOUNT solution (and to answer the 
>> obvious question, I can't use a database at work).
>
> I think I'd use SUMPRODUCT(). Suppopse your second table starts in 
> AA1.  Then try something like:
> =SUMPRODUCT($B$2:$B$999=AB$1;$E$2:$E$999=$AA2)
>
> The individual parameters are logical expressions, and taking the 
> product of these ANDs them.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>


Thanks, though I'll have to look this up more:

=SUMPRODUCT(Sales.$B$2:$B$655=C$1;Sales.$E$2:$E$655=$A2)  returns zero 
even when there is data

Sales has the Sales data
C1 has the date to check
B is the column in sales with the date
A2 has the Emp ID
E is the column in sales with the Employee ID.



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org


Mime
View raw message