Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm working w/ excel 2003/professional. I'm trying to get my first tab to count all entries made in tab 2 where column B contains "FY10/02" and column J contains "STATE" Can anyone give me a formula to count the entries? I know that I definetly have 3 state entries for FY10/02, but it keeps returning blank.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Adjust your ranges as necessary.
    =SUMPRODUCT(--(Sheet2!B1:B11="FY10/02"),--(Sheet2!J1:J11="State"))

  3. #3
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried the formula:
    =sumproduct(('sheet2'!$B$3:$B$65536="FY10/02"),('sheet2'!$J$3:$J$65536="State"))

    And it returned 0 in the cell. I even tried retyping FY10/02 and State, and making sure there weren't any spaces afterwards..just incase. And it's still not counting the 3 entries? I also have one Federal entry too (switching state to federal) that is not being recognized Any ideas? Thank you so much for all your help!!!!!

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula you posted is not the same as the one I posted.

    Change it to this
    =sumproduct(--('sheet2'!$B$3:$B$65536="FY10/02"),--('sheet2'!$J$3:$J$65536="State"))

    or this

    =SUMPRODUCT((Sheet2!$B$3:$B$65536="FY10/02")*(Sheet2!$J$3:$J$65536="State")*1)

  5. #5
    Star Lounger
    Join Date
    Feb 2004
    Location
    Jacksonville, Florida
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    The formula you posted is not the same as the one I posted.

    Change it to this
    =sumproduct(--('sheet2'!$B$3:$B$65536="FY10/02"),--('sheet2'!$J$3:$J$65536="State"))

    or this

    =SUMPRODUCT((Sheet2!$B$3:$B$65536="FY10/02")*(Sheet2!$J$3:$J$65536="State")*1)

    Perfect, worked wonderfully! Thank you again so much!!!!!!... for future reference, what does the -- do?

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Turns the True or False to 1 or 0 for summing.
    Do a search on "unary excel" for the complete explanation.

Posting Permissions

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