COUNTIFS - Single Cell Multiple Results

User avatar
Joseph
3StarLounger
Posts: 206
Joined: 31 Dec 2010, 22:23
Location: Columbia Falls, MT

COUNTIFS - Single Cell Multiple Results

Post by Joseph »

Hello, I have a string of data in cell A2

Example:
A2
"Late Fee: 31-60 minutes: $50.00 = 50.00, Late Fee: 31-60 minutes: $50.00 = 50.00, Processing Support: $3.00 = 3.00"

I need to determine a count for each time a given rate was used.

Using the COUNTIF only returns the 1st instance.
=COUNTIFS(A2,"*Late Fee: 31-60 minutes*")=1 (Should be 2 based on the example)

What solution would return the desired result of 2?

Thanks!

User avatar
Joseph
3StarLounger
Posts: 206
Joined: 31 Dec 2010, 22:23
Location: Columbia Falls, MT

Re: COUNTIFS - Single Cell Multiple Results

Post by Joseph »

I did some Googling and determined the formula I needed.

=SUMPRODUCT((LEN('Revenue Report'!$V:$V)-LEN(SUBSTITUTE('Revenue Report'!$V:$V,$K56,"")))/LEN($K56))

Problem is this returns the total result, I need it to run based on a specific location and date, which I was able to do with the COUNTIFS like so
=COUNTIFS('Revenue Report'!$V:$V,"*"&K57&"*",'Revenue Report'!$A:$A,'Look Up'!$F$2,'Revenue Report'!$AL:$AL,Unloading!$J$55)

How can I add criterias to the 1st formula?

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: COUNTIFS - Single Cell Multiple Results

Post by Rudi »

Hi Joseph,

If I have the context correct, how about this:
Adjust references as needed.

=SUMPRODUCT(--(A3="A"),--(B3=DATEVALUE("2014-4-20")),(LEN(C3)-LEN(SUBSTITUTE(C3,"Late Fee: 31-60 minutes","")))/LEN("Late Fee: 31-60 minutes"))

See sample workbook.
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Joseph
3StarLounger
Posts: 206
Joined: 31 Dec 2010, 22:23
Location: Columbia Falls, MT

Re: COUNTIFS - Single Cell Multiple Results

Post by Joseph »

Thanks Rudi, this solved it, and saved my rear!!