+ Reply to Thread
Results 1 to 8 of 8

Count two columns based on different criteria for each

  1. #1
    Registered User
    Join Date
    09-03-2010
    Location
    Washington
    MS-Off Ver
    Excel 2007
    Posts
    14

    Count two columns based on different criteria for each

    So I'm trying to add up two columns based on criteria from each to give me a total. From below I would like to add up all that have say CMDY and all that have a Verified time larger then 16:00. The answer should be 12, 2 from CMDY and 10 greater then the verified time of 16:00.

    I have tried =sumproduct and =countif but neither seems to give me the results I'm looking to get. Maybe I'm missing something in my formulas but I don't get a number I get an error. I attached a .xls what what it looks like in excel. I cleaned the attachment up a little so the formula below has a little different range.

    This is what I have been trying to get working but
    =SUMPRODUCT(G20:G144=CMDY)*(N20:N144>=Verified: 09/15/10 16:00))


    006 54989A HD Forte 9.30 SSEA AENN 16:00-24:00 09/15/10 19:15:30 09/19/10 Verified: 09/15/10 19:16

    016 54989A HD Forte 9.30 SSEA CMDY 06:00-24:00 09/15/10 16:18:30 09/19/10 Verified: 09/15/10 16:22

    020 54989A HD Forte 9.30 SSEA CMDY 16:00-24:00 09/15/10 21:40:30 09/19/10 Verified: 09/15/10 21:33

    024 54989A HD Forte 9.30 SSEA CNNN 06:00-24:00 09/15/10 22:31:30 09/19/10 Verified: 09/15/10 22:44

    028 54989A HD Forte 9.30 SSEA CNNN 16:00-24:00 09/15/10 20:50:30 09/19/10 Verified: 09/15/10 20:56

    066 54989A HD Forte 9.30 SSEA FSNW 16:00-24:00 09/15/10 22:45:00 09/19/10 Verified: 09/15/10 22:31

    070 54989A HD Forte 9.30 SSEA FXXX 06:00-24:00 09/15/10 15:55:00 09/19/10 Verified: 09/15/10 15:57

    082 54989A HD Forte 9.30 SSEA FXNC 16:00-24:00 09/15/10 19:20:30 09/19/10 Verified: 09/15/10 19:19

    086 54989A HD Forte 9.30 SSEA HIST 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:42

    094 54989A HD Forte 9.30 SSEA LIFE 06:00-24:00 09/15/10 06:20:00 09/19/10 Verified: 09/15/10 06:28

    107 54989A HD Forte 9.30 SSEA SPKK 06:00-24:00 09/15/10 07:29:30 09/19/10 Verified: 09/15/10 07:28

    123 54989A HD Forte 9.30 SSEA TBSC 06:00-24:00 09/15/10 13:45:00 09/19/10 Verified: 09/15/10 13:42

    127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 18:27:30 09/19/10 Verified: 09/15/10 18:16

    127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 19:45:30 09/19/10 Verified: 09/15/10 19:46

    139 54989A HD Forte 9.30 SSEA TNTT 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:40

    143 54989A HD Forte 9.30 SSEA TNTT 16:00-24:00 09/15/10 16:31:30 09/19/10 Verified: 09/15/10 16:15
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,224

    Re: Count two columns based on different criteria for each

    With your test data:

    =SUMPRODUCT(--(D1:D16="CMDY")*--(I1:I16>="Verified: 09/15/10 16:00"))

    Regards

  3. #3
    Registered User
    Join Date
    09-03-2010
    Location
    Washington
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Count two columns based on different criteria for each

    That gives me a answer of 2 but it should be 12, it seems to be only counting CMDY and not counting the other criteria.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Count two columns based on different criteria for each

    Maybe you want this then...

    =SUMPRODUCT(--(D1:D16="CMDY")+(I1:I16>="Verified: 09/15/10 16:00"))

    Edit: Actually that shouldn't work either. If you are trying to count column I as time you will first have to seperate the data time from the verified because right not it simply is a text string.

    I seperated I into column J placed 9/15/2010 in E18 and then used...

    =SUMPRODUCT(--(D1:D16="CMDY")+(J1:J16>=E18))
    Attached Files Attached Files
    Last edited by jeffreybrown; 09-28-2010 at 08:09 PM.

  5. #5
    Registered User
    Join Date
    08-06-2009
    Location
    Japan
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Count two columns based on different criteria for each

    try to use conditional sum
    Tools --> conditional sum (need to install conditional sum wizard as add-ins )

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,224

    Re: Count two columns based on different criteria for each

    Sorry, I misread your requirement. If you want 12 as your answer, try:

    =SUMPRODUCT(--(D1:D16="CMDY"))+SUMPRODUCT(--(I1:I16>="Verified: 09/15/10 16:00"))

    SUMPRODUCT is normally used as an AND operator, multiplying arrays together. That's how you originally specified it ( =SUMPRODUCT(G20:G144=CMDY)*(N20:N144>=Verified: 09/15/10 16:00)) ).
    The * multiplies the results of the two arrays ... you want to add them.

    Probably more efficient if you want to add the results is:

    =COUNTIF(D1:D16,"CMDY")+COUNTIF(I1:I16,">=Verified: 09/15/10 16:00")

    Regards

  7. #7
    Registered User
    Join Date
    09-03-2010
    Location
    Washington
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Count two columns based on different criteria for each

    Thanks for the help, I left it alone for a while and I realized that I was giving the wrong information to get the wrong answer. I guess I was working on it to long. I attached a new work.xls. Its that same idea but I'm looking to count the spots that were on CMDY column G and that are greater then 16:00 column N. Originally I was asking to all all spots that had CMDY and all that were greater then 16:00 so my answer was not right. It was counting all the CMDY spots and and all the greater then spots. So in the new example the answer I'm looking to get is 3. Even though there are 4 CMDY's only 3 of them were greater then 16:00. I plan on creating a macro that will give me that same info for all the others in column G, like ESPN will be 3. I was using the string of "Verified: 09/10/10 16:00" for my greater then. Since that has text and numbers is that ok? If not, is there a way to mass edit and remove the Verified portion, some of the reports are really big. I was to only add the two columns if both conditions are meet.

    Thanks for all the help
    Attached Files Attached Files
    Last edited by king12yan; 09-29-2010 at 02:03 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,224

    Re: Count two columns based on different criteria for each

    Back to the original formula then ... just changing the ranges and the date string:

    =SUMPRODUCT(--(G1:G126="CMDY")*--(N1:N126>="Verified: 09/10/10 16:00"))

    If you wanted to get rid of the "Verified: " you could do a Replace All. In 2007: Find & Select | Replace ... | Find what: "Verified: " (without the quotes) | Replace with: (leave blank) | Replace All

    Regards

+ 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