sumif

[Sulasno]

still trying to find a way to use 2 criteria within 2 ranges to add 1 range
of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give twice
the amount

 

[macropod]

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in
that way. For that you could use the SUMPRODUCT function (see the other replies), or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or
whole-row references (ie E:E or 3:3 won't work).

Cheers

 

[Sulasno]

thanks
I am trying to understand what does the * means

going to try this forumula;

=sum(if((A1:A100,"XXX")*((B1:B100,"YYY"),C1:C100)) and use
<Ctrl>-<Shift>-<Enter>)

can "XXX" and "YYY" point to a value in a cell?
=sum(if((A1:A100,X1((B1:B100,Y1),C1:C100)) and use <Ctrl>-<Shift>-<Enter>)

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that way.
For that you could use the SUMPRODUCT function (see the other replies),
or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[Sulasno]

thanks
finally got it right

{=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))}

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that way.
For that you could use the SUMPRODUCT function (see the other replies),
or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[Don Guillett]

Less resource use with sumproduct.
=SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

thanks
finally got it right

{=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))}

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that way.
For that you could use the SUMPRODUCT function (see the other replies),
or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[Sulasno]

thanks
is there a limit to the number of criteria?

I presume that this will also work?
=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2)*($D$2:$D$7=D2),$E$2:$E$7)

Less resource use with sumproduct.
=SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

thanks
finally got it right

{=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))}

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that way.
For that you could use the SUMPRODUCT function (see the other replies),
or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[Don Guillett]

You should always try it yourself first.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

thanks
is there a limit to the number of criteria?

I presume that this will also work?
=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2)*($D$2:$D$7=D2),$E$2:$E$7)

Less resource use with sumproduct.
=SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

thanks
finally got it right

{=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))}


Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that
way. For that you could use the SUMPRODUCT function (see the other
replies), or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[Sulasno]

I wish I could; I don't have the file in hand and is helping someone with
the question

You should always try it yourself first.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]

thanks
is there a limit to the number of criteria?

I presume that this will also work?
=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2)*($D$2:$D$7=D2),$E$2:$E$7)

Less resource use with sumproduct.
=SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
thanks
finally got it right

{=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))}


Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND
range2=criteria2. SUMIF won't work with multiple conditions in that
way. For that you could use the SUMPRODUCT function (see the other
replies), or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a
fully-qualified range - you can't use simple whole-column or whole-row
references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1
range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give
twice the amount

 

[macropod]

Hi Sulasno,

The tests (range1=criteria1) and (range2=criteria2) both return True/False (1/0) values according to whether the test is satisfied.
So each test ends up with and array of 1s and 0s. The * multiples the 1s and 0s from the two arrays. The result is that you end up
with 1s (ie True results) where the values in both arrays are 1s (ie 1*1=1, 1*0=0, 0*1=0, 0*0=0). In essence, then, * works like an
AND test - basically requiring both conditions to be true.

Sorry about the typo in the formula. It should have been:
=SUM(IF((range1=criteria1)*(range2=criteria2),data))

And yes, you can add more conditions, for example:
=SUM(IF((range1=criteria1)*(range2=criteria2)*(range2>criteria1)*(range1<>criteria2),data))

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

thanks
I am trying to understand what does the * means

going to try this forumula;

=sum(if((A1:A100,"XXX")*((B1:B100,"YYY"),C1:C100)) and use <Ctrl>-<Shift>-<Enter>)

can "XXX" and "YYY" point to a value in a cell?
=sum(if((A1:A100,X1((B1:B100,Y1),C1:C100)) and use <Ctrl>-<Shift>-<Enter>)

Hi Sulasno,

I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in
that way. For that you could use the SUMPRODUCT function (see the other replies), or:
=SUM(IF((range1=criteria1)*((range2=criteria2),data))
as an array formula (entered with <Ctrl>-<Shift>-<Enter>).

Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or
whole-row references (ie E:E or 3:3 won't work).

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data;

range1 range2 criteria1 criteria2 data

=sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give twice the amount