As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Excel/OpenOffice Calc formula issue

The CatThe Cat Registered User, ClubPA regular
edited February 2007 in Help / Advice Forum
OK, what I'm trying to do is get a formula to return the number of instances in a worksheet where one column has Value X and the column next to it has Value Y.

So, if figured I could use COUNTIF with multiple criteria. Apparently this doesn't work, only Excel 2007 can handle that by using a new function called COUNTIFS. That's nice, but I work for the government and therefore won't see Office 2007 until maybe 2020. Is there a workaround to get what I need in Excel or OOCalc?

tmsig.jpg
The Cat on

Posts

  • Options
    NerissaNerissa Registered User regular
    edited February 2007
    Try this:

    Make a column with the following formula:

    =IF(A=X, IF(B=Y,TRUE,FALSE), FALSE)

    where A and B are the appropriate column / cell references

    Then do a COUNTIF(C, TRUE) where C is the range of cells containing the above formula.

    If necessary, you can hide the column to make your worksheet pretty.

    Nerissa on
  • Options
    The CatThe Cat Registered User, ClubPA regular
    edited February 2007
    works awesomely, but I'm going to have to repeat that around 20 times in order to tabulate the data properly :P damn excel...

    thanks!

    The Cat on
    tmsig.jpg
  • Options
    Knuckle DraggerKnuckle Dragger Explosive Ovine Disposal Registered User regular
    edited February 2007
    =IF((A=X)*(B=Y),true,false) should work as well.

    Knuckle Dragger on
    Let not any one pacify his conscience by the delusion that he can do no harm if he takes no part, and forms no opinion.

    - John Stuart Mill
Sign In or Register to comment.