using SUMIF function with compound conditions

[agarwaldvk]

Can I use the SUMIF function with compound conditions like so :-

"=sumif(and((index(namedRange1,,1),"a"),(index(namedRange1,,2),"b")),index(namedRange1,1,3))",


the idea being to add values in column 3 of the named range namedRange
for those rows only where both of specified conditions are true i.e. th
value in column1 = "a" and the value in column2 = "b".


I now know I can use the above with single condition as shown below :-

=sumif(index(namedRange1,,1),"a",index(namedRange1,1,3))

but that doesn't seem to help here!

I could get it going by typing in the formula as below :-

"{=SUM(IF(INDEX(namedRange1,,1)="a",IF(INDEX(namedRange2,,2)="b",INDEX(namedRange1,,3),0),0))}
-> entered as an array formula!

but I would rather use sumif with compound conditions, if I could!


Any help would be greatly appreciated!


Best regards



Deepak Agarwa

 

[Ragdyer]

Try this:

=SUMPRODUCT((INDEX(rng1,,1)="A")*(INDEX(rng1,,2)="B")*INDEX(rng1,,3))

 

[Biff]

Hi Deepak!

Try this:

=SUMPRODUCT(--(INDEX(namedRange1,,1)="a"),--(INDEX
(namedRange1,,2)="b"),INDEX(namedRange1,,3))

Biff

 

[agarwaldvk]

Does it work with wild cards too? I couldn't get it going with wild car
in the condition within the double quotes e.g "*A*"

Best regards



Deepak Agarwa

 

[agarwaldvk]

Hey Biff

Any thoughts on the wild cards in the condition as indicated in m
latest query?


Best regards



Deepak Agarwa

 

[Frank Kabel]

Hi
if you really nead wildcards (such as '*' and '?') try the following
(adapt ranges to your needs):
=SUMPRODUCT(--(COUNTIF(OFFSET(INDEX(sheet1!$A$1:$A$10,1,1),ROW(sheet1!$
A$1:$A$10)-CELL("row",sheet1!$A$1:$A$10),0),"*a*")),--(COUNTIF(OFFSET(I
NDEX(sheet1!$B$1:$B$10,1,1),ROW(sheet1!$B$1:$B$10)-CELL("row",sheet1!$B
$1:$B$10),0),"*b*")),--sheet1!$C$1:$C$10)

 

[Biff]

Hi Deepak!

Sumproduct by itself will not accept wildcards. However,
you can nest a function that does use wildcards within the
Sumproduct.

Exactly what are you trying to do?

Biff

 

[agarwaldvk]

Frank

I will try and understand your solution - its a bit of a mouthful fo
me at the moment. If you could please explain that to me a little bit
it would help in my understanding but I shall definitely give it a tr
tomorrow morning at work. I don't have the file here at home to try i
now!

In the meanwhile, Biff, this is exactly what I am trying to do :-

I have some texts in columns say "a" and column "b" and some values i
column "c". I have a named range which includes columns a, b and c.

The values in column "a" are something like this :-

" Advantage Plus N"
" Plus Advantage N"
" Advantage Plus Z" etc.

In other words, the only thing certain about these values in column "a
is that they are sure to contain the word "advantage" in the
somewhere.

Similarly, the values in column "b" are this :-

"NSW"
"ACT"
"VIC" etc.

For example, I want to add the values in column c only for those row
in the named range example the values in column "b" is "NSW" and th
value in column "a" contain the word "advantage".

So, I had a formula like so (as suggested by those who certainly kno
better than I do) :-

=sumproduct((index(namedRange1,,1)="*Advantage*"),(index(namedRange1,,2)="NSW"),index(namedRange1,,3))

This did not work. I am not particularly familiar with sumproduct(
function and hence didn't try other options with it. But if you o
anyone has anything else to suggest, I am willing to give it a go.

I then tried this :-

=sumif(and(index(namedRange1,,1)="*Advantage*",index(namedRange1,,2)="NSW"),index(namedRange1,,3))

and this :-

=sumif((index(namedRange1,,1)="*Advantage*"
index(namedRange1,,2)="NSW"),index(namedRange1,,3))

and this :-

=if(and(index(namedRange1,,1)="*Advantage*",index(namedRange1,,2)="NSW"),sum(index(namedRange1,,3)),0)

and even this :-

=if(index(namedRange1,,1)="*Advantage*",if(index(namedRange1,,2)="NSW"),sum(index(namedRange1,,3)),0),0)


Neither of the above 4 solutions worked.

Where am I going wrong? Any further suggestion?????????


Best regards


Deepak Agarwa

 

[RagDyeR]

Without using your named ranges, and just referencing the columns (ranges)
involved, you could try this:

=SUMPRODUCT((ISNUMBER(SEARCH("advantage",A1:A100)))*(B1:B100="NSW")*C1:C100)

You could of course substitute the "literals" (advantage, NSW), with cell
addresses, so that you could change the criteria without having to change
the formula itself.

=SUMPRODUCT((ISNUMBER(SEARCH(G1,A1:A30)))*(B1:B30=G2)*C1:C30)

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Frank

I will try and understand your solution - its a bit of a mouthful for
me at the moment. If you could please explain that to me a little bit,
it would help in my understanding but I shall definitely give it a try
tomorrow morning at work. I don't have the file here at home to try it
now!

In the meanwhile, Biff, this is exactly what I am trying to do :-

I have some texts in columns say "a" and column "b" and some values in
column "c". I have a named range which includes columns a, b and c.

The values in column "a" are something like this :-

" Advantage Plus N"
" Plus Advantage N"
" Advantage Plus Z" etc.

In other words, the only thing certain about these values in column "a"
is that they are sure to contain the word "advantage" in them
somewhere.

Similarly, the values in column "b" are this :-

"NSW"
"ACT"
"VIC" etc.

For example, I want to add the values in column c only for those rows
in the named range example the values in column "b" is "NSW" and the
value in column "a" contain the word "advantage".

So, I had a formula like so (as suggested by those who certainly know
better than I do) :-

=sumproduct((index(namedRange1,,1)="*Advantage*"),(index(namedRange1,,2)="NS
W"),index(namedRange1,,3))

This did not work. I am not particularly familiar with sumproduct()
function and hence didn't try other options with it. But if you or
anyone has anything else to suggest, I am willing to give it a go.

I then tried this :-

=sumif(and(index(namedRange1,,1)="*Advantage*",index(namedRange1,,2)="NSW"),
index(namedRange1,,3))

and this :-

=sumif((index(namedRange1,,1)="*Advantage*" +
index(namedRange1,,2)="NSW"),index(namedRange1,,3))

and this :-

=if(and(index(namedRange1,,1)="*Advantage*",index(namedRange1,,2)="NSW"),sum
(index(namedRange1,,3)),0)

and even this :-

=if(index(namedRange1,,1)="*Advantage*",if(index(namedRange1,,2)="NSW"),sum(
index(namedRange1,,3)),0),0)


Neither of the above 4 solutions worked.

Where am I going wrong? Any further suggestion?????????


Best regards


Deepak Agarwal

 

[hgrove]

First, don't leave the subject box blank in excelforum. Enter th
original subject line possibly adding 'Re: ' at the beginning. Failur
to use the same subject line (modulo leading Re screws up Googl
Groups message threading.

agarwaldvk wrote...

I have some texts in columns say "a" and column "b" and some
values in column "c". I have a named range which includes
columns a, b and c. ...
For example, I want to add the values in column c only for those >row

in the named range example the values in column "b" is

"NSW" and the value in column "a" contain the word "advantage". ...
=sumproduct((index(namedRange1,,1)="*Advantage*"),
(index(namedRange1,,2)="NSW"),index(namedRange1,,3))

...

This won't work. The comparison terms like

(index(namedRange1,,2)="NSW")

*MUST* contain some arithmetic operation that will convert the boolea
values these comparisons return into the integers 1 and 0. The doubl
unary minuses, --, in Biff's response, which you seem to have ignore
or misunderstood, perform this function. In general, it's a bad idea t
ask for help then ignore the bits & parts you don't understand. Follo
the solutions you receive EXACTLY and IN FULL. If they don't work, the
follow up. If they do work, then modify them at your own risk.

I then tried this :-

=sumif(and(index(namedRange1,,1)="*Advantage*",
index(namedRange1,,2)="NSW"),index(namedRange1,,3))

and this :-

=sumif((index(namedRange1,,1)="*Advantage*"
+ index(namedRange1,,2)="NSW"),index(namedRange1,,3))

...

These WILL NOT WORK. You haven't read online help. SUMIF can accep
only ranges as first and third arguments, and the condition must appea
as the second argument. This isn't an optional arrangement.

Your IF(...SUM(...)) constructs are also doomed to failure, but fo
other reasons - the SUM result would always be the same for each an
every TRUE result in the array first argument to IF.

You can't use SUMIF for this, so stop trying to do so.

You could use either SUM(IF(...)) or SUMPRODUCT.

=SUMPRODUCT(--(SUBSTITUTE(INDEX(namedRange1,0,1),
"Advantage","")<>INDEX(namedRange1,0,1)),
--(INDEX(namedRange1,0,2)="NSW"),INDEX(namedRange1,0,3))

The double unary minuses are NECESSARY. If you omit them, don't b
surprised if you get a zero result

 

[agarwaldvk]

Hi Frank

As promised, I tried your solution this morning and it works. This i
fantastic.

Educate me here a bit. I WANT TO KNOW! Tell me why does it work.
don't understand but would like to know instead of just using it.

I am not particularly familiar with SUMPRODUCT() function, howeve
since I have got to use the same to some extent in the last few days
(thanks to some of solutions provided by yourself, Biff, Harlan an
Ragdyer) I believe I now have some understanding and appreciation o
the same - but obviously not quite as much as I would like or need.

Greatly appreciated.


Harlan

How am I supposed to know that the title needs to rewritten whe
posting a reply. It clearly says that it is optional. Secondly, it onl
stands to reason that it your are "replying" then the title would b
carried forward. This aspect is however noted for future reference.

I am not so sure if you being judgemental about me is going to be o
any help to anybody especially when you may not be fully aware of th
situation. I might be inclined to acknowledge that you might be the B
ALL AND END ALL when it comes to Excel but such condescending notes o
yours don't show you in the best light. If you want furthe
clarifications on the subject, I can do so on or off the forum.

Best regards



Deepak Agarwa

 

[agarwaldvk]

Hi Frank

As promised, I tried your solution this morning and it works. This i
fantastic.

Educate me here a bit. I WANT TO KNOW! Tell me why does it work.
don't understand but would like to know instead of just using it.

I am not particularly familiar with SUMPRODUCT() function, howeve
since I have got to use the same to some extent in the last few days
(thanks to some of solutions provided by yourself, Biff, Harlan an
Ragdyer) I believe I now have some understanding and appreciation o
the same - but obviously not quite as much as I would like or need.

Greatly appreciated.


Harlan

How am I supposed to know that the title needs to rewritten whe
posting a reply. It clearly says that it is optional. Secondly, it onl
stands to reason that it your are "replying" then the title would b
carried forward. This aspect is however noted for future reference.

I am not so sure if you being judgemental about me is going to be o
any help to anybody especially when you may not be fully aware of th
situation. I might be inclined to acknowledge that you might be the B
ALL AND END ALL when it comes to Excel but such condescending notes o
yours don't show you in the best light. If you want furthe
clarifications on the subject, I can do so on or off the forum.

Best regards



Deepak Agarwa

 

[hgrove]

agarwaldvk wrote...
...

How am I supposed to know that the title needs to rewritten
when posting a reply. It clearly says that it is optional. Secondly,
it only stands to reason that it your are "replying" then the title
would be carried forward. This aspect is however noted for
future reference.

excelforum is not USENET. What excelforum believes is optional onl
shows a certain ignorance on the part of its maintainer. It's als
really stupid that replying in a thread doesn't automatically bring th
thread's original subject as the reply's subject. But the excelforu
people may only care about how the messages are threaded within thei
own forum.

I am not so sure if you being judgemental about me is going to
be of any help to anybody especially when you may not be fully
aware of the situation.

No respondent is ever fully aware of an OP's situation because no O
ever provides truly complete details.

As for being judgmental, there were objective facts at issue.

1. Biff showed double unary minuses in his reponse, You omitted them i
your follow-up. Either that was carelessness on your part, or you faile
to understand their use and thought you could omit them. There's n
other likely reason.

2. Online help is clear about what SUMIF requires for arguments.
little investigation on your part could have shown that what you ha
tried to use as first arguments in SUMIF weren't ranges.

I might be inclined to acknowledge that you might be the BE ALL
AND END ALL when it comes to Excel but such condescending
notes of yours don't show you in the best light.

You may discover that this doesn't matter to me. Or you may fail t
discover it. Doesn't matter to me.

If you want further clarifications on the subject, I can do so on
or off the forum.

Apparently unnecessary