Re: [libreoffice-users] Calc Function

Sorry, I accidentally sent my message to James instead of the mailing list.
 Let me recap what we discussed off-list.

James E Lang wrote:
> Correction: I meant SUMIF() rather than SUM().
>
> Corrected formula:
>
> =IF(___(A$3:A7,A7,Q$3:Q7)=___(A$3:A7,A7,R$3:R7),SUMIF(A$3:
A7,A7,R$3:R7)-SUMIF(A$3:A7,A7,Q$3:Q7),"Error 4")

If I understand the formula correctly, you simply would not need a 3rd
argument.  As I see it, the formula is looking at the values in column A
and checking which of those values matches the condition in A7.  Then you
want the formula to count the cells in column Q where the match occurred.
 But it does not matter which cells you count, because you only count the
matches in column A (which has nothing to do with column Q or R).

It seems difficult to explain what I mean, let me try an illustration:
There are 5 apples (3 red and 2 green) and 5 pears.  Each apple has a
number from 1 to 5 and each pear has a number from 1 to 5.  Count the pears
that have numbers that corrospond to red apples.  The answer would be 3.
 But you could just have counted how many apples are red.

Maybe you just want something like this:
=IF(countif(Q$3:Q7,A7) = countif(R$3:R7,A7),SUMIF(A$3:
A7,A7,R$3:R7)-SUMIF(A$3:A7,A7,Q$3:Q7),"Error 4")

That said, it is difficult to visualise the goal of the formula.  I suspect
I don't quite know what you want.  If you could upload a sample document
(with sensitive data removed) it might be easier to understand.

Regards
Stephan

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted