+ Reply to Thread
Results 1 to 4 of 4

Formula half working. IF( Countifs)

  1. #1
    Registered User
    Join Date
    12-01-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    2

    Formula half working. IF( Countifs)

    Ok, so first of all I'm going to place the formula here but my excel version is in portuguese so after placing the formula i'll put a little index on bot to explain the differences.

    =SE(Matches!$C:$C="Yes";CONTAR.SE.S(Matches!$D:$D;'Hero Use '!A3;Matches!$F:$F;'Hero Use '!A3;Matches!L:L;'Hero Use '!A3;Matches!$N:$N;'Hero Use '!A3;Matches!$U:$U;'Hero Use '!A3);CONTAR.SE.S(Matches!$E:$E;'Hero Use '!A3;Matches!$G:$G;'Hero Use '!A3;Matches!$M:$M;'Hero Use '!A3;Matches!$O:$O;'Hero Use '!A3;Matches!$T:$T;'Hero Use '!A3))

    SE = IF
    CONTAR.SE.S = COUNTIFS (I think...)

    So I have 2 spreadsheets, "Hero Use" and "Matches". On the "Matches" spreadsheet I manually input data.
    On the "Hero Use" I have this formula which I want it to go fetch info from the "Matches" spreadsheet to fill itself up.

    First thing I want the formula to do is check the column C on "Matches" and see if it is "Yes" or "No", if it is "Yes" then I want it to check columns D, F , L, N and U; If it's "No" I want it to check columns E, G, M, O and T.

    Now I want to check these columns to see if it matches the name on "Hero Use" spreadsheet in column A. If it does, then I want it to Count it and keep adding it up. I will overtime fill in more "Matches" info and I want it to keep counting.

    I guess that is it. If anyone is able to help me and needs more info let me know.

    Thank you all in advance.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula half working. IF( Countifs)

    Hi,

    a different approach using SUMPRODUCT (SOMARPRODUTO) in order to count A3 if are in column D or F...U.


    Please Login or Register  to view this content.
    Hope it helps


    Saudações
    Last edited by canapone; 12-01-2013 at 11:54 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    12-01-2013
    Location
    Portugal
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Formula half working. IF( Countifs)

    Maybe that works but the formula you posted doesn't I'll try to work around your suggestion.

    Thanks for the suggestion anyway.

    Any other ideas?

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Formula half working. IF( Countifs)

    Hi,

    countifs add 1 only if A3 is -example- if d2,f2,l2,n2,u2=a3
    sumproduct add 1 if a3 is only in d2.

    it's like a serie of countif (CONT.SE)

    =SE(Matches!$C:$C="Yes";cont.se(Matches!$D1:$D1000;'Hero Use '!A3)+cont.se(Matches!$F1:$F1000;'Hero Use '!A3)+cont.se(Matches!$L1:$L1000;'Hero Use '!A3)+cont.se(Matches!$N1:$N1000;'Hero Use '!A3)+cont.se(Matches!$U1:$U1000;'Hero Use '!A3);cont.se((Matches!$E1:$E1000;'Hero Use '!A3)+cont.se(Matches!$G1:$G1000;'Hero Use '!A3)+cont.se(Matches!$M1:$M1000;'Hero Use '!A3)+cont.se(Matches!$O1:$O1000;'Hero Use '!A3)+cont.se(Matches!$T1:$T1000;'Hero Use '!A3))

    If the formula gives back the expected result we can work on it.

    If you'd need to count the number of columns containing at least one A3, we adjust the formula.

    Maybe you can upload a sample file with some example.

    Ciao
    Last edited by canapone; 12-01-2013 at 12:20 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Countifs FOrmula Not working
    By cartica in forum Excel General
    Replies: 1
    Last Post: 10-30-2013, 01:34 PM
  2. [SOLVED] countifs working on extended ranges and different sheets not working
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 02:23 PM
  3. vba copy paste only half working
    By Mummraah in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-31-2012, 07:29 AM
  4. [SOLVED] Formula to calculate if employee is working on each half hour
    By holowugz in forum Excel General
    Replies: 10
    Last Post: 07-16-2012, 08:29 PM
  5. time and a half working a holiday
    By whitelighter in forum Excel General
    Replies: 5
    Last Post: 09-21-2010, 11:26 AM

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