Counting Multivalued Fields Using Countifs

[jki9496]

Hello,

I have a column with multiple values imported from access.

A1: 4;10;8
A2: 5;4;1
A3: 2;13;4
A4: 4

I need to count how many times each value appears in the list. I am trying to use the countifs statement because I only want to count values who meet criteria defined in a seperate column on a different worksheet.

The formula looks like this:

=COUNTIFS(Sheet1!C:C,"No",Sheet1!D:D,4)

In this example, when I run the formula it only returns a count of 1 when clearly there are actually three 4's. It seems to not be counting the values in the multivalued field.

Any help is GREATLY, GREATLY appreciated as my boss is looking for this report tomorrow!

Mary Beth

 

[Domenic]

Try...

=SUMPRODUCT(--(C2:C100="No"),--ISNUMBER(FIND(";"&F2&";",";"&D2:D100&";")))

...where F2 contains 4. Adjust the ranges, accordingly.

 

[jki9496]

from the bottom of my VERY stressed out heart..thank you!!! It worked perfectly!!!!!!

 

[jki9496]

One other question.

Is there a way to have a user input a date range to control the number of rows being counted? I have the creation date for the row in column F. I know I can do this on a form in access using VB but i'm not sure how it would be applied in Excel.

Thank you!

 

[Domenic]

Try...

=SUMPRODUCT(--($C$2:$C$100="No"),--ISNUMBER(FIND(";"&$I$2&";",";"&$D$2:$D$100&";")),--($F$2:$F$100>=$J$2),--($F$2:$F$100<=$K$2))

...where I2 contains 4, J2 contains the starting date, and K2 contains the ending date.