+ Reply to Thread
Results 1 to 4 of 4

SUMIF - Multiple Conditions

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479

    SUMIF - Multiple Conditions

    I am using excel 2000

    I have a spreadsheet with multiple columns

    Column C is Store Name
    Column G is Staff Name
    Column N is FTE

    I need a formula that looks at Column C and for all the stores called A, then looks at Column G and excludes all entries containing "Vacant" and then totals the entries in Column N that correspond

    I guess it requires some sort of SUMIF or SUMPRODUCT type formula, but having a mental block and can't get my head around it this morning

    Any help gratefully received
    Paul

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Is this waht you need?
    ( you will have to adapt the range)

    =sumproduct(--(c1:c100="a"),--(ISERROR(SEARCH("VACANT",G1:G100))),(n1:n100))
    Last edited by arthurbr; 08-23-2007 at 04:55 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Thanks arthurbr

    Just before you posted had a brainstorm and got the following to work

    =SUMPRODUCT(--(Staffing!$C$4:$C204=$C4),--(Staffing!$G$4:$G204<>"vacant"),(Staffing!$N$4:$N204))

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    OK, I didn't understand that the word "vacant" was on it's own but thought it was part of a string.
    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1