+ Reply to Thread
Results 1 to 4 of 4

CountifS for Column Reference, Row Reference and Data Validation Reference

  1. 12-24-2018, 01:53 AM #1
    pavanbhoyar
    pavanbhoyar is offline
    Registered User
    Join Date
    03-12-2014
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    53

    Post CountifS for Column Reference, Row Reference and Data Validation Reference

    Hello,
    I have data as per attached excel sample.
    Data sheet is a complete spreadsheet of detailed data and Summary sheet need to extract data from "Data" sheet.
    In summary sheet, I need to count "Available" and "Shortage" as per conditions matching in Column B and Data validation cell "C1".
    Column B contains fix data. Data in cell "C1" is variable which is being changed using data validation.
    Cell "C1" in summary sheet should match with row 1 in data sheet and Column "B" in summary sheet should match with Column "A" in data sheet.

    I hope problem description is sufficient enough to understand problem.

    Thanks in advance.!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-24-2018, 01:58 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: CountifS for Column Reference, Row Reference and Data Validation Reference

    In C4 copied across and down:

    =SUMPRODUCT((Data!$B$2:$G$913=C$3)*(Data!$B$1:$G$1=Summary!$C$1)*(Data!$A$2:$A$913=Summary!$B4))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 12-24-2018, 02:12 AM #3
    pavanbhoyar
    pavanbhoyar is offline
    Registered User
    Join Date
    03-12-2014
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: CountifS for Column Reference, Row Reference and Data Validation Reference

    It worked for me.!
    Thanks for the help.
    Reply With QuoteRegister To Reply
  4. 12-24-2018, 02:37 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: CountifS for Column Reference, Row Reference and Data Validation Reference

    You're welcome. Merry Christmas!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Countifs - For Row and Column Reference
    By pavanbhoyar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-24-2018, 01:13 AM
  2. [SOLVED] First column as reference, rest of columns as listing to reference
    By samuroeh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-19-2016, 02:05 PM
  3. A calculated column changes a table reference to a cell reference when I reopen the file
    By krabine in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2015, 02:49 AM
  4. Column number reference using cell reference
    By Fatrobo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-16-2015, 12:50 AM
  5. dragging formula across a row with absolute column reference but relative row reference
    By merlyn45 in forum Excel General
    Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  6. Row reference increment but preserve column reference
    By Pwanda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2005, 09:06 AM
  7. Macro to Reference Column Next to Current Reference
    By dolphinv4 in forum Excel General
    Replies: 2
    Last Post: 04-11-2005, 04:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1