• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count and Sum by matching multiple values in a single cell.

M

msharma864512

Member
Hi,

I want to calculate count and sum by matching certain conditions. However, if one cell has multiple entries separated by comma, then also it should consider that value.

Details are in the attachment.
 

Attachments

  • Count and Sum by matching multiple values from a single cell.xlsx
    10 KB · Views: 17
You can use in column G:
=COUNTIF($B$3:$B$12,"*"&F15&"*")
your description seems to be incorrect in the file (central appears 4 times and not 5 times)

And in column H following formula can be used.
=SUMIF($B$3:$B$12,"*"&F15&"*",$C$3:$C$12)
 
shrivallabha said:
You can use in column G:
=COUNTIF($B$3:$B$12,"*"&F15&"*")
your description seems to be incorrect in the file (central appears 4 times and not 5 times)

And in column H following formula can be used.
=SUMIF($B$3:$B$12,"*"&F15&"*",$C$3:$C$12)
Click to expand...

Thanks for the formula,

but there is only issue with this, for West, it is also considering Southwest, For. e.g West count should have been 4, but the formula is returning 6, because it is also counting Southwest, but it should not.
 
Finding the word boundaries makes it bit tricky.
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1)))
and for totals
=SUMPRODUCT(ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1))*$C$3:$C$12)
 
shrivallabha said:
Finding the word boundaries makes it bit tricky.
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1)))
and for totals
=SUMPRODUCT(ISNUMBER(SEARCH(" "&F15&" "," "&SUBSTITUTE($B$3:$B$12,","," ")&" ",1))*$C$3:$C$12)
Click to expand...
This is great, now its working fine,, Thank u very much
 
msharma864512 said:
This is great, now its working fine,, Thank u very much
Click to expand...
The formulas you provided was really great and helpful.

But now, I want to apply one more condition to count and sum

The details are in the attached spreadsheet.

Regards,
Manish
 

Attachments

  • Count and Sum by matching multiple values from a single cell.xlsx
    10.9 KB · Views: 4
hi @msharma864512 ,


See if is it ok ?
Using Power Query And pivot Table will achieve Result
Please follow below Staps,
1) Convert Raw Data in Table or Name Range
2)Using Power Query Data will be Arrange data Correct Format so will analaye Data
3)Then insert Pivot table
Regard
Rahul Shewale
 

Attachments

  • Count and Sum by matching multiple values from a single cell (1).xlsx
    33.4 KB · Views: 7
rahulshewale1 said:
hi @msharma864512 ,


See if is it ok ?
Using Power Query And pivot Table will achieve Result
Please follow below Staps,
1) Convert Raw Data in Table or Name Range
2)Using Power Query Data will be Arrange data Correct Format so will analaye Data
3)Then insert Pivot table
Regard
Rahul Shewale
Click to expand...
Thanks Rahul for giving it a shot.

I already have the pivots to the analysis.

But now i dont to have multiple pivots, the analysis i am looking for requires 1000s of pivots, hence i am trying to get rid of pivots and shift to formulas to make it simpler.

Regards,
Manish
 
msharma864512 said:
The formulas you provided was really great and helpful.

But now, I want to apply one more condition to count and sum

The details are in the attached spreadsheet.

Regards,
Manish
Click to expand...
You sample is inconsistent. Cell D10 shall be "PE, PE, PE". It could well be consistent for human to identify this as PE for all but it is too much to deal for a code. If you have office 365 and TEXTJOIN function then you can try which can be copied down and across.
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$15)*99,99))=$F15)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$15)*99,99))=N$14))
 
shrivallabha said:
You sample is inconsistent. Cell D10 shall be "PE, PE, PE". It could well be consistent for human to identify this as PE for all but it is too much to deal for a code. If you have office 365 and TEXTJOIN function then you can try which can be copied down and across.
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$15)*99,99))=$F15)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$15)*99,99))=N$14))
Click to expand...
Thankyo Shrivallabha for providing me a great formula, and belv me this is way above my pay grade to understand the logic, but still i will try to understand the same.

I have coped the formula to my spreadsheet, and this is working fine for deal count, but when I used the same formula to calculate deal values, the result is not correct.
To calculate the deal values, I have just added the column reference of the deal values.

But please note, in case in any row, there are more than one region/PE or Corp, it should add the deal value separately for every region.

Regards,
Manish
 

Attachments

  • Count and Sum by matching multiple values from a single cell.xlsx
    11.7 KB · Views: 3
Hi Manish,
Your requirement is quite unique for me (personally). The formula approaches sometimes become tedious to deal with as you are finding out. I am curious about cell B10 where West, West is appearing. What does it mean? 10.9 shall be added twice?
 
Try........

1] 1 way conditional Count, G4 copied down :

=SUMPRODUCT(1-ISERR(SEARCH(" "&F4&","," "&B$3:B$12&",")))

2] 1 way conditional Sum, H4 copied down :

=SUMPRODUCT((1-ISERR(SEARCH(" "&F4&","," "&B$3:B$12&",")))*C$3:C$12)

3] 2 ways conditional Count, I4 copied right to J4 and all copied down :

=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=$F4)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=I$3))

4] 2 ways conditional Sum, K4 array formula (Ctrl+Shift+Enter) copied right to L4 and all copied down :

=SUM(IFERROR(((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=$F4)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=K$3))*INDEX($C$3:$C$12,N(IF(1,MATCH(ROW($A$1:$A$20)-1,MMULT(N(ROW($1:$11)>COLUMN($A:$J)),LEN($B$3:$B$12)-LEN(SUBSTITUTE($B$3:$B$12,",",""))+1))))),""))

67145
 

Attachments

  • Count and Sum by matching multiple values from a single cell (BY).xlsx
    15.3 KB · Views: 9
Last edited:
bosco_yip said:
Try........

1] 1 way conditional Count, G4 copied down :

=SUMPRODUCT(1-ISERR(SEARCH(" "&F4&","," "&B$3:B$12&",")))

2] 1 way conditional Sum, H4 copied down :

=SUMPRODUCT((1-ISERR(SEARCH(" "&F4&","," "&B$3:B$12&",")))*C$3:C$12)

3] 2 ways conditional Count, I4 copied right to J4 and all copied down :

=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=$F4)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=I$3))

4] 2 ways conditional Sum, K4 array formula (Ctrl+Shift+Enter) copied right to L4 and all copied down :

=SUM(IFERROR(((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$B$3:$B$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=$F4)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",1,$D$3:$D$12),",",REPT(" ",99)),ROW($A$1:$A$20)*99,99))=K$3))*INDEX($C$3:$C$12,N(IF(1,MATCH(ROW($A$1:$A$20)-1,MMULT(N(ROW($1:$11)>COLUMN($A:$J)),LEN($B$3:$B$12)-LEN(SUBSTITUTE($B$3:$B$12,",",""))+1))))),""))

View attachment 67145
Click to expand...

Thank you Bosco, this is really awesome.

Thank you very much for all the helo.
 
This is my suggestion, which differs only a little bit in its concept.

In cell B1:
A formula is implemented to keep track of all cells having commas and provide entries
=SUMPRODUCT(IF(LEN(B3:INDEX(B:B,MATCH("zz",B:B,1)))=0,0,LEN(B3:INDEX(B:B,MATCH("zz",B:B,1)))-LEN(SUBSTITUTE(B3:INDEX(B:B,MATCH("zz",B:B,1)),",",""))+1))

In cell C1:
A formula is kept for calculating the last value filled in column B
=MATCH("zz",B:B,1)

So the volume formula in cell M15 suggested earlier becomes
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$B$3:INDEX($B:$B,$C$1)),",",REPT(" ",99)),ROW($A$1:INDEX($A:$A,$B$1))*99,99))=$F15)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$D$3:INDEX($D:$D,$C$1)),",",REPT(" ",99)),ROW($A$1:INDEX($A:$A,$B$1))*99,99))=M$14))

And the value formula in cell P15 becomes
=SUMPRODUCT((TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$B$3:INDEX($B:$B,$C$1)),",",REPT(" ",99)),ROW($A$1:INDEX($A:$A,$B$1))*99,99))=$F15)*(TRIM(MID(SUBSTITUTE(","&TEXTJOIN(",",TRUE,$D$3:INDEX($D:$D,$C$1)),",",REPT(" ",99)),ROW($A$1:INDEX($A:$A,$B$1))*99,99))=P$14)*MID(SUBSTITUTE(","&TEXTJOIN("",TRUE,REPT($C$3:INDEX($C:$C,$C$1)&",",LEN($B$3:INDEX($B:$B,$C$1))-LEN(SUBSTITUTE($B$3:INDEX($B:$B,$C$1),",",""))+1)),",",REPT(" ",99)),ROW($A$1:INDEX($A:$A,$B$1))*99,99))

The intention behind this is to provide a dynamic setup which will allow you to populate columns B through D without having to edit the formulas. I am attaching the workbook.

Since you had mentioned usage of formulas, the formula implementation is shown but my suggestion would be to use a VBA based UDF as the formulas are extremely unwieldy from maintenance viewpoint. I'd leave that call to you.
 

Attachments

  • Count and Sum by matching multiple values from a single cell_demo.xlsx
    13.3 KB · Views: 3
msharma864512 said:
Thank you Bosco, this is really awesome.

Thank you very much for all the helo.
Click to expand...
Hi Bosco,

I have tried using the formulas you provided in my model, however when i am expanding the formula to more data, its not giving any result.

Please see in the attachment.

Regards,
Manish
 

Attachments

  • Count and Sum by matching multiple values from a single cell (BY).xlsx
    123.8 KB · Views: 4
msharma864512 said:
Hi Bosco,
I have tried using the formulas you provided in my model, however when i am expanding the formula to more data, its not giving any result.
Please see in the attachment.
Manish
Click to expand...

This is part of your formula extracted from your file ….......TEXTJOIN(",",1,$B$3:$B$5513)…........

As per Microsoft Office Support mentioned :
https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Your formula with 5511 range of cell which appear exceeding the limitation, maximum of 252 range of cell (text arguments).

Regards
 
Last edited:
Are these more or less correct?
Your data at the top, an expanded table in the middle, and a pivot based on that expanded table at the bottom:
2020-04-27_181333.jpg
 
p45cal said:
Are these more or less correct?
Your data at the top, an expanded table in the middle, and a pivot based on that expanded table at the bottom:
View attachment 67388
Click to expand...

Thanks P45cal,

I already have Power Query in Place, and using Pivots to do the similar analysis, but now i am trying to shift to formulas, as my earlier file has lots of bug, hence wanted to get rid of that.

Regards,
Manish
 
You should never need 1000s of pivots!
If you're going to replace 1000s pivots with formulae you'll need tens of thousands of formulae, even a million or more?
If they're all going to be of the kind so far suggested by others, that's going to be a BIG load on:
  • the workbook
  • people using the workbooks waiting for it to recalculate
  • memory
  • people maintaining the formulae
and anyway, as you've found, the formulae are proving to be buggy.

If your Power Query/Pivot is buggy, it's more than likely they can be improved/corrected.
Power Query and Pivots have been designed to deal quickly with bigger data.
@bosco_yip has suggested that a formula solution won't help - and he's pretty good with formulae. Take his advice.

My question in msg#20 was to confirm that the results were correct, that's all, so that people designing solutions can know whether they're getting the right results from ANY kind of manipulation, formulae/queries/pivots; you didn't answer.
 
p45cal said:
You should never need 1000s of pivots!
If you're going to replace 1000s pivots with formulae you'll need tens of thousands of formulae, even a million or more?
If they're all going to be of the kind so far suggested by others, that's going to be a BIG load on:
  • the workbook
  • people using the workbooks waiting for it to recalculate
  • memory
  • people maintaining the formulae
and anyway, as you've found, the formulae are proving to be buggy.

If your Power Query/Pivot is buggy, it's more than likely they can be improved/corrected.
Power Query and Pivots have been designed to deal quickly with bigger data.
@bosco_yip has suggested that a formula solution won't help - and he's pretty good with formulae. Take his advice.

My question in msg#20 was to confirm that the results were correct, that's all, so that people designing solutions can know whether they're getting the right results from ANY kind of manipulation, formulae/queries/pivots; you didn't answer.
Click to expand...
Thanks P45Cal for detailed explanation.

Firstly, to answer ur question in msg#20, yes the result is correct in both the approaches.

And unfortunately, my model has ~1,045 pivots, and I completely agree replacing them with formulas is not a good move. And yes when Bosco has given some advise, i will certainly take into consideration.

Hence, for now I have decided to move model to Alteryx and Power BI to have more streamlined output.

Regards,
Manish
 
You must log in or register to reply here.
Back
Top