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!
COUNTIFS - Single Cell Multiple Results
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: COUNTIFS - Single Cell Multiple Results
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?
=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?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: COUNTIFS - Single Cell Multiple Results
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.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 206
- Joined: 31 Dec 2010, 22:23
- Location: Columbia Falls, MT
Re: COUNTIFS - Single Cell Multiple Results
Thanks Rudi, this solved it, and saved my rear!!