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.

 

[VoG]

Try

=SUMPRODUCT(--(C1:C100="Overview"),--(D1:D100="Yes"))

 

[Dannyh1]

Hi Try:

=SUMPRODUCT((C1:C7="Overview")*(D1:D7="Yes"))

 

[T. Valko]

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.

Try one of these...

Using cells to hold the criteria:

• F2 = Overview
• G2 = Yes

This formula will work in ALL versions of Excel:

=SUMPRODUCT(--(C2:C8=F2),--(D2:D8=G2))

This formula will work in Excel versions 2007 and later:

=COUNTIFS(C2:C8,F2,D2:D8,G2)

 

[SRGsMom]

Nope -- I did this: =sumproduct(--(c1:c5000="Overview"),--(D1:D5000="Yes"))

and I get the "The formula you typed contains an error." message box

 

[VoG]

That works for me.

 

[T. Valko]

Nope -- I did this: =sumproduct(--(c1:c5000="Overview"),--(D1:D5000="Yes"))

and I get the "The formula you typed contains an error." message box

That formula is syntactically correct so I don't know what Excel might be complaining about.

Does it highlight a section of the formula?

 

[SRGsMom]

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

 

[taurean]

Nope -- I did this: =sumproduct(--(c1:c5000="Overview"),--(D1:D5000="Yes"))

and I get the "The formula you typed contains an error." message box

I had read on this forum, your language settings cause an error (it was referring to "dutch" language I suppose) and the suggestion was to use ";" instead of "," (different character). Otherwise the above formula works fine.

 

[VoG]

Yes, if you use , as the decimal separator you need to replace , with ;

Excel Workbook
	C	D	E
1	Overview	Yes	2
2	gg	Yes
3	overview	yes
Sheet1