Count two columns based on different criteria for each

**king12yan** · 09-28-2010, 07:08 PM

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

**TMS** · 09-28-2010, 07:19 PM

With your test data:

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

Regards

**king12yan** · 09-28-2010, 07:27 PM

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.

**jeffreybrown** · 09-28-2010, 07:43 PM

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))

**smalltime** · 09-28-2010, 09:19 PM

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

**TMS** · 09-29-2010, 03:40 AM

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

**king12yan** · 09-29-2010, 01:52 PM

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

**TMS** · 09-29-2010, 03:16 PM

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