• 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.

shrivallabha

Excel Ninja
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)
 
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)
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.
 

shrivallabha

Excel Ninja
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)
 
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)
This is great, now its working fine,, Thank u very much
 
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
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
 

shrivallabha

Excel Ninja
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
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))
 
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))
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

shrivallabha

Excel Ninja
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?
 

bosco_yip

Excel Ninja
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

Last edited:
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
Thank you Bosco, this is really awesome.

Thank you very much for all the helo.
 

shrivallabha

Excel Ninja
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

bosco_yip

Excel Ninja
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
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:

p45cal

Well-Known Member
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
 
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
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
 

p45cal

Well-Known Member
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.
 
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.
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
 
Top