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

SUMPRODUCT / INSTR

Per Claussen

Per Claussen

Member
Hello, I have four columns: A - TRUE/FALSE, B - String of 19 characters, C - String of 2 characers, D - amount.
Could anybody please help me produce a formula which sums D-column amounts if A is true, character 17 in B-column is 3 or 4, and C-value is "XX"?
 
Hi Per Claussen,

Welcome to Chandoo.org forum.

Suppose you values in A1:D2, try using below formula:

=SUMPRODUCT((A1:A2=TRUE)*(C1:C2="XX")*(D1:D2)*(IFERROR(FIND({3,4},B1:B2,1)=17,FALSE)))

Regards,
 
Here's another variation which uses MID() to get the 17th character:

=SUMPRODUCT(--A2:A5,--ISNUMBER(MATCH(MID(B2:B5,17,1),{3,4}&"",0)),--(C2:C5="XX"),D2:D5)
 
Even better, if you have XL 2007 or later you can do this with SUMIFS().

=SUM(SUMIFS(D2:D5,A2:A5,TRUE,B2:B5,REPT("?",16)&{3,4}&"??",C2:C5,"XX"))
 
thanks a lot, both of you - I couldn't get the first one to take any care about the 17th digit, but the second formula made it. Now what if that 17th character is not 3 or 4, but the letter V?
 
If you want the 17th character to only be a "V" then the formula is simpler:

=SUMPRODUCT(--A2:A5,--(MID(B2:B5,17,1)="V"),--(C2:C5="XX"),D2:D5)

or

=SUMIFS(D2:D5,A2:A5,TRUE,B2:B5,REPT("?",16)&"V??",C2:C5,"XX")

Neither are case sensitive.
The SUMIFS() formula is faster and also validates that the words in column C are exactly 19 characters.
 
Colin Legg said:
If you want the 17th character to only be a "V" then the formula is simpler:

=SUMPRODUCT(--A2:A5,--(MID(B2:B5,17,1)="V"),--(C2:C5="XX"),D2:D5)

or

=SUMIFS(D2:D5,A2:A5,TRUE,B2:B5,REPT("?",16)&"V??",C2:C5,"XX")

Neither are case sensitive.
The SUMIFS() formula is faster and also validates that the words in column C are exactly 19 characters.
Click to expand...
Thanks again - in fact I'm trying to analyze and sum amounts in different SAP WBS-elements. This WBS-code is 19 characters: nnnnnnnnn.x.XX1.Znn, - where XX is either "AC", "EL" of "PI". The 17th char can be both digit or character. The 17th character specifies what kind of WBS it is, and so do ZZ. Its of course my B column which contains the WBS-codes.
It would be very convenient to be able to use the same formula for all combinations, but not a must
 
In that case I think your best bet would be to add a helper column with a formula which extracts the 17th character. A formula like this copied down the column:
=MID(B2,17,1)

You can also use additional helper columns to extract any other data of interest.

Once you've done that you can use a pivot table to slice and dice and summarise different combinations.
 
Tried different formula for the 1st problem of Per. No, they are not efficient.
Was just trying to get the results by different method.

=IF(AND(A1=TRUE,C1="XX",IF(SUM(IF(LEFT(RIGHT(B1,3),1)=CHAR(51),1,0)+IF(LEFT(RIGHT(B1,3),1)=CHAR(52),1,0))=1,TRUE,FALSE)=TRUE),D1,0)

=IF(AND(IF(OR(LEFT(SUBSTITUTE(B1,LEFT(B1,16),""),1)*1=3,LEFT(SUBSTITUTE(B1,LEFT(B1,16),""),1)*1=4),TRUE)=TRUE,IF(A1=TRUE,TRUE)=TRUE,IF(C1="XX",TRUE)=TRUE),D1,0)
 

Attachments

  • Book1.xls
    25.5 KB · Views: 3
Colin Legg said:
Here's another variation which uses MID() to get the 17th character:

=SUMPRODUCT(--A2:A5,--ISNUMBER(MATCH(MID(B2:B5,17,1),{3,4}&"",0)),--(C2:C5="XX"),D2:D5)
Click to expand...

Hello again - the above formula was perfect for me, as long as I was using checkboxes in range A2:A5. Now these checkboxes are history, as I have decided to use the "Marlett checkbox" variant instead.
How would this formula look like when A2:A5 now contains an "a" when "checked"?
 
=SUMPRODUCT(--(A2:A5="a"),--ISNUMBER(MATCH(MID(B2:B5,17,1),{3,4}&"",0)),--(C2:C5="XX"),D2:D5)

or, SUMIFS which is faster...

=SUM(SUMIFS(D2:D5,A2:A5,"a",B2:B5,REPT("?",16)&{3,4}&"??",C2:C5,"XX"))

or, DSUM() per Deb's post #16 which is even faster...

just change the TRUE in F3 to a
 
thanks a lot for helping me out, Colin :) I have tried to get the SUMIFS formula to work, without any luck. Could you please take look at the attached file, and enter your formula and see if you succeed?
 

Attachments

  • PCtestWBS.xlsx
    10.2 KB · Views: 3
Hi Per Claussen
Please check the attached..
just followign @Colin Legg 's suggestion..

=SUMIFS($D$2:$D$22,$A$2:$A$22,"A",$B$2:$B$22,G$1,$C$2:$C$22,REPT("?",16)&{3,6}&"*")
Try to change BOLD area, as per requirement..
 

Attachments

  • PCtestWBS.xlsx
    10.3 KB · Views: 0
Your Type and Wbs columns (columns B and C) are switched around in the file, so the first formula (check "a", type "AC", wbs position 17 "3" or "6") would be like

=SUMPRODUCT(--(A2:A22="a"),--ISNUMBER(MATCH(MID(C2:C22,17,1),{3,6}&"",0)),--(B2:B22="AC"),D2:D22)

or

=SUM(SUMIFS(D2:D22,A2:A22,"a",C2:C22,REPT("?",16)&{3,6}&"??",B2:B22,"AC"))

Both give me a result of 600000.
 
Colin Legg said:
Your Type and Wbs columns (columns B and C) are switched around in the file, so the first formula (check "a", type "AC", wbs position 17 "3" or "6") would be like

=SUMPRODUCT(--(A2:A22="a"),--ISNUMBER(MATCH(MID(C2:C22,17,1),{3,6}&"",0)),--(B2:B22="AC"),D2:D22)

or

=SUM(SUMIFS(D2:D22,A2:A22,"a",C2:C22,REPT("?",16)&{3,6}&"??",B2:B22,"AC"))

Both give me a result of 600000.
Click to expand...
Still doesn't work for me. I have Excel 2010 norwegian, so I have to change commas to semicolon, but it doesn't help. Could you please be kind and fill in the four AC formulas, and post the file, so I can play with it?
 
thanks - when I opened this file, the formulas were translated to norwegian automatically, and now works. Thanks a lot, Colin. :) :)
 
You must log in or register to reply here.
Back
Top