Need some Count If help....

[SRGsMom]

I have looked and looked for a resolution, this has to be simple and I hope I'm overthinking it.

I have used sumif, countif, sumproduct, etc....nothing works.


Column C has training names in it, values could be Overview, Claims, Enrollments, etc.

Column D has a Yes or No in it (meaning did the attendee show up or not)

I want to Count the Yes responses in column D, IF the training name is Overview in column C.


So...
Overview Attended -- Want a # Here (should be 3)

Column C Column D
Trg Name Attendeed
Overview Yes
Overview Yes
Claims Yes
Enrollments Yes
Overview No
Overview Yes
Claims Yes

(I previewed the post and I see my "example" didn't work out so well, hope that isn't too confusing)

Your help would be GREATLY appreciated! I do know that on some array formulas you have to do the Control+Shift+Enter, and I'm still not getting it right.

 

[T. Valko]

I redid the formula with the suggestion above putting Overview and Yes in seperate cells, both times, when it complains, it highlights the comma between the C argument and D argument.

So I took the comma out, tried again and it gives me the #Value! error.

New Formula: =SUMPRODUCT(--C1:C5000=G3)--(D1:D5000=H2)
Also Tried: =SUMPRODUCT(--C1:C5000="Overview")--(D1:D5000="Yes")

Depending on your location your regional settings may use a semi-colon instead of a comma.

See taurean's reply.

 

[SRGsMom]

Still not getting it -- I think I need to give it a rest!

Using this: =SUMPRODUCT(--(C9:C5000="Overview")--(D9:D5000="Yes"))

It WILL NOT take a , or ;

Now I get a total of 142, there are only 89 records in the spreadsheet right now.

Of those, only 59 have attended the Overview Training. 15 have attended the claims training, and 15 registered attendees didn't show up at all.

 

[taurean]

Go to Start >> Settings >> Control Panel Then choose "Regional and Language options" and see what settings are applied.

 

[SRGsMom]

It's using English (US)

 

[T. Valko]

It's using English (US)

Does one of these array entered** formulas give you the correct result?

=SUM(IF(C9:C5000="Overview",IF(D9:D5000="Yes",1)))

=SUM(IF(C9:C5000="Overview";IF(D9:D5000="Yes";1)))

=SUM((C9:C5000="Overview")*(D9:D5000="Yes"))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[taurean]

OK. How the number is represented. Is it as shown below?

123,456,789.00

 

[SRGsMom]

Yes, exactly as you wrote.

 

[VoG]

Did you enter the formula exactly as shown? The -- is two consecutive minus signs.

 

[SRGsMom]

I have entered this:

=SUMPRODUCT(--(C9:C5000="Overview")--(D9:D5000="Yes")) -- which returns 142, the wrong value


=SUMPRODUCT(--(C9:C5000="Overview"),--(D9:D5000="Yes"))
=SUMPRODUCT(--(C9:C5000="Overview");--(D9:D5000="Yes"))

Both of which Excel didn't like.

 

[T. Valko]

I have entered this:

=SUMPRODUCT(--(C9:C5000="Overview")--(D9:D5000="Yes")) -- which returns 142, the wrong value


=SUMPRODUCT(--(C9:C5000="Overview"),--(D9:D5000="Yes"))
=SUMPRODUCT(--(C9:C5000="Overview");--(D9:D5000="Yes"))

Both of which Excel didn't like.

Did you try any of the array formulas I suggested in reply #15?