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

SUMIF/SUMIFS/SUMPRODUCT with multiple conditions for the same column [SOLVED]

S

supero

New Member
Hi all


A tricky one that I could not crack this morning.

Say that you have this list:

type £ Code

R1 6562 A

R2 8502 B

R3 3673 C

R4 8420 B

R5 5975 A

R6 3457 G

R6A 7224 H

R6B 7258 J


and you want to sumif, column 2 if the criteria for column 3 is {A,B}

now you can do that if you use {A,B} in your criteria.


However i need is to convert the string into something that I can use in the formulae


Say that is another column I would have the criteria

Something like

Criteria

A

A,B

G,H,A


What I need is the SUMIF/SUMIFS/SUMPRODUCT to point at the cell that has the criteria (the criteria differs cell by cell, in number of conditions too) and make the calculation, so converting the string into the criteria for the formula


I hope I explained this well enough and that someone can help me, and this is now bugging me all day.


Many thanks


Oscar
 
=SUMPRODUCT((B2:B9)*(C2:C9={"A","B"}))


or


=SUMPRODUCT((B2:B9)*(C2:C9=Criteria))

where Criteria is a named formula as = {"A","B"}
 
HI Hui


Thanks a lot for your fast reply, although it is not really what I am after.

I guess I explained it wrong.


The criteria column is dinamic, I can not define Criteria as a static range, otherwise I have to retype it for each of the criterias which could be as many as N.


The criteria is dinamic

so the criteria cell will say: A,B and the sumif needs to use this criteria

so if the criteria says: A,B,G, then the sumif/sumproduct needs to use this condition


Hope this makes it clearer


Many thanks


Oscar
 
I forgot to mention that if the criteria cell says: A, B

I am using a concatenate to make it a selection, something like ="{"&B12&"}" where B12= "A","B"
 
If the criteria is all single digits/characters like your example, you could do:

=SUMPRODUCT((ISNUMBER(SEARCH(C2:C12,CriteriaCell)))*(B2:B12))


Formula is just checking each Code to see if it's located somewhere in the criteria cell.
 
Actually that kind of works. Thank you Luke

However i would need to change the Criteriacell each time.

Imagine criteria column like this

Criteria

{"A","B"}

{"A","G","H"}


what I want is to use your formula that change the criteria cell as you copy down

so, 1) =SUMPRODUCT((ISNUMBER(SEARCH(C2:C12,CriteriaCell)))*(B2:B12)) where CriteriaCell = {"A","B"}


And when copy down this is what you now get:

2) =SUMPRODUCT((ISNUMBER(SEARCH(C2:C12,CriteriaCell)))*(B2:B12)) where CriteriaCell = {"A","G","H"}


thanks a lot

Oscar
 
Sorry, I didn't explain that well. CriteriaCell should be a reference. Let's say the criteria are listed in col D, and all the data looks like:

[pre]
Code: 
type	£	Code	Crit.	Formula Result
R1	6562	A	A,B	29459
R2	8502	B	A,B,C	33132
R3	3673	C	G,H	10681
R4	8420	B	J	7258
R5	5975	A
R6	3457	G
R6A	7224	H
R6B	7258	J
[/pre]
Formula would then be:

=SUMPRODUCT((ISNUMBER(SEARCH($C$2:$C$12,D2)))*($B$2:$B$12))

You don't need to have the Brackets, quotation marks, or anything now. Formula is just looking if the letter is listed within the cell somewhere.
 
Excellent. Thank you so much Luke!


And add-in to that question


That works great is what you are looking for the exact value, A's and/or B's

What is the code column would have now types of transaction and your criteria would look at whether it is part of the type or not


something like this, replacing the 2 columns for these 2:

[pre]
Code: 
Code	          Crit.
AR Cash P	Cheque, DD
Cash B	        DD
DD P	        RR, Cash, DD
DD B	        RR
RR Cheque C
RR DD
Cash B
RR Cheque C
[/pre]
So, the criteria would be all that contain the word cheque or DD


can this be done?


Thanks a lot
 
I don't quite understand your example. Could you add another column to the example showing what your expected answer(s) would be?
 
thank you Luke


How about this?

[pre]
Code: 
type	£	Code	        Crit.	                Formula Result
R1	6562	AR Cash P	Cheque, DD	        28783
R2	8502	Cash B	        DD	                18737
R3	3673	DD P	        RR, Cash, DD	        51071
R4	8420	DD B	        RR	                16690
R5	5975	RR Cheque C
R6	3457	RR DD
R6A	7224	Cash B
R6B	7258	RR Cheque C
[/pre]
The formula would be something like this

sumifs($B$1:$B$9,CRITERIA(anything that contains Cheque and DD, pointing at the cell),$C$1:$C$9)


post edited with backticks
 
Goo day supero


You keep moving the goal posts, a sure fire way of p**sing the forum member of, all your questions in this post have been answered but then you add to the original post.

Members give their time for free with no reward other than the satisfaction of answering questions and helping members, alongside this they have to live their lives, family, work, sleep, ect ect.

It would help if you thought through what you need and then post, It is difficult at times to try and work out the end result but a bit of forethought will help the members to understand your needs without wasting their time by the original post be altered Ad infinitum.


You have added cheque's and cash to your code/criteria......but you have not taken in to account that you may barter and you would need to include X number of sheep, Y number of Horses, Z number of cases of Carlsberg, U number of apples.... :)


Members are only to willing to help just take some time to think it through and give them a fighting chance to help you.
 
Hmm. Well, first, I will point out that things would be much easier if each criteria had it's own cell, rather than putting it all in a single cell. That said, I wsa able to produce the result using a User Defined Function (UDF). To install, right click on sheet tab, view code. Then go to Insert - Module, paste this in.

[pre]
Code: 
Function SuperSumIF(CritRange As Range, Criteria As String, SumRange As Range) As Double
Dim AllCrit, MyCrit, MySum, MyChoice

Dim runningTotal As Double
Dim i As Long

'Assumes you use a comma as delimiter
AllCrit = Split(Criteria, ",")

'Generate our arrays
MyCrit = CritRange.Value
MySum = SumRange.Value
ReDim MyChoice(1 To CritRange.Count)

For i = 0 To UBound(AllCrit)
For x = 1 To CritRange.Count
If InStr(1, MyCrit(x, 1), Trim(AllCrit(i)), vbTextCompare) Then
MyChoice(x) = True
End If
Next x
Next i

For i = 1 To UBound(MyChoice)
If MyChoice(i) Then
runningTotal = runningTotal + MySum(i, 1)
End If
Next i

SuperSumIF = runningTotal
End Function
[/pre]
Close the visual basic editor (VBE) and in your workbook, the formula is:

=SuperSumIF($C$2:$C$9,D2,$B$2:$B$9)
 
Wow. Amazing!! Thank you Luke.. it works like a treat!!

Amazing stuff!


bobhc - sorry. I was not trying to waste anyone's time. I really appreciate Luke's time and effort, and I really hope that his solution will help many others in similar scenarios.
 
You must log in or register to reply here.
Back
Top