CODEWITHSUNDEEP
excelexcel-formula
Moosli
Moosli

Reputation: 3285

Counting the Amount with Multiplication Condition

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?

Upvotes: 0

Views: 198

Answers (2)

J. D.
J. D.

Reputation: 1571

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.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26660

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

Upvotes: 4

Related Questions