Counting the Amount with Multiplication Condition

0
votes

I have an Excel List That Looks Like This:

enter image description here

And i need to Count the Amount of Rows that have the Value Finished in Row C. But if T Value in Column B Contains a Semikolon it hase to Count double, if it Contains 2 Semikolons it hase to count 3 and so on...

Her is a Picture to show how the Counting should work.

enter image description here

I will not be able to Add Another Column to the Sheet, so i'm Locking for a Matrix Fomula or something like this.

Till now i tried it with the Countif and Countifs Formula but that didn't work. Can someone give me a hint how to do thsi or waht Fomula would be the best to use?

2
excelexcel-formula
I am a big believer of using formulas in excel instead of VBA.. where possible. But I cannot think of a formula that would do that. Is VBA not an option? - Zac
For the sake of simplicity, I would use a separate column D to count the number of semi-colons in each cell and then do a sumproduct - Dan
@Dan Yeah i thought aswell of a seperated Column, but i thought there has to be a way to do it with a formula. ^^ - Moosli

2 Answers

4
votes

For your "Finished" total, use this formula (adjust ranges as necessary to fit your actual data):

=SUMPRODUCT((LEN($B$5:$B$28)-LEN(SUBSTITUTE($B$5:$B$28,";",""))+1)*($C$5:$C$28="Finished"))

For your "Pending" total, if it needs the same treatment, use this formula:

=SUMPRODUCT((LEN($B$5:$B$28)-LEN(SUBSTITUTE($B$5:$B$28,";",""))+1)*($C$5:$C$28="Pending"))
1
votes

Are you allowed to use/create a new worksheet (other than the one containing the data)? If yes, you can always compute the values you need for this to work in a separate worksheet, then use cell referencing to pull the value back into your original workbook. So, in a Sheet2 (for example), I would have two columns:

PENDING: =IF(Sheet1!$C4="Pending",1+LEN(Sheet1!$B4)-LEN(SUBSTITUTE(Sheet1!$B4,";","")),0)

FINISHED: =IF(Sheet1!$C4="Finished",1+LEN(Sheet1!$B4)-LEN(SUBSTITUTE(Sheet1!$B4,";","")),0)

Fill that formula as far down as needed.

Then, on your original sheet where you would like to enter the sums (I called it Sheet1 in the formulae above, you would do a =SUM(Sheet2!$B$1:$B$25) to populate the Amount Pending cell and =SUM(Sheet2!$A$1:$A$25) for the Amount Finished.

** If you are not allowed to use a new worksheet, I would say a VB script would be the only other choice.

EDITED Best solution is the SUMPRODUCT formula offered previously.