Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.