1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula

Discussion in 'Business Applications' started by Winger, Nov 8, 2003.

Thread Status:
Not open for further replies.
  1. Winger

    Winger Thread Starter

    Joined:
    Dec 5, 2000
    Messages:
    27
    Currently increasing a field (AJ2) based on certain criteria (If the designated cells (C5:C20 has and "X" in any of the column cells) (AJ2) would increase by (1) Now need to expand that criteria to include some additional entries as indicated at the bottom but exclude an entry of "N/C".

    Current senerio;
    AJ2 =SUMPRODUCT((MOD(COLUMN(C38:AZ38),2)=1)*C38:AZ38)

    C38 =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,"X")>0))

    Need to expand the "X" in range C5:C20 to not be limited to just "X" but be expanded to include one of 10 specific text inputs like AWL, E/P, E/V or (7 other selected text inputs)

    Thank you in advance
    Winger
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    Winger, I cannot for the life of me work out ½ of what you're trying to do here. For instance, COLUMN(C38:AZ38) just gives you the column # for C:C, ie: 3.

    This is a complete stab in the dark, but how about if you amended to something like

    COUNTIF(C5:C20,{"X","Y","Z"})

    ?

    HTH,
    Andy
     
  3. Winger

    Winger Thread Starter

    Joined:
    Dec 5, 2000
    Messages:
    27
    I"ll try to explain but not necessary as you fixed it with the { } parenthesis (Whatever they are called). The sheet counts AM and PM classes for students, based on entries in the 5 trough 20 cells. It figures percentage of attendance, and unexcused entries but only if a date is entered in the date ROW for that column and an "X" appears for any student in the column. We needed it to accept more that just an "X" as acceptable entry and disregard a N/C (No Class), and now does NOT figure class session if only one student on the entire sheet and we enter a N/C. All other entries will still indicate a class was held and the AM and PM number will increase. The row you questioned was actually =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,{"X","AWL","E*","U*"})>0)) It checked C3:AZ3 for content and C38:AX38 would check 5 through 20 for an acceptable entry If found the value would change from "0" to "1". (Got this one from this site also) The counter was dependant on both the results (Date and row 38 results. 5 through 20 can now have several entries besides "X" and still count as a class, Thanks to you, N/C is NOT one of those entries.

    Thanks again.
    100% correct again
    (Your good)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/177824

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice