V
vbaexcel
Hi
=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),
(Data1!$E$1:$E$500))
In plain english the formula above means: Sum cells E1 to E500 (last
argument) if in B1 to B500 the value is "March" and if in C1 to C500
the value is "New York".
SUMPRODUCT is like SUMIF on steroids and it allows you to automate all
these reports that you develop with data that you import from a large
centralized database, centralized accounting, manufacturing and sales
programs or the Internet.
The last argument (preceded by a comma) tells Excel what to sum and
the two previous arguments (separated by an asterisk) tell Excel about
the conditions for summing.
You can do this with SUMPRODUCT and much more.
Within a table where months are in column "A" and cities in row "11"
the formula would read like this
=SUMPRODUCT((Data1!$B$1:$B$500=$A13)*(Data1!$C$1:$C$500=B$11),(Data1!$E
$1:$E$500))
Visit: http://www.excel-examples.com/00-excel-function-sumproduct.htm
Rediscover Excel and enjoy
=SUMPRODUCT((Data1!$B$1:$B$500="March")*(Data1!$C$1:$C$500="New York"),
(Data1!$E$1:$E$500))
In plain english the formula above means: Sum cells E1 to E500 (last
argument) if in B1 to B500 the value is "March" and if in C1 to C500
the value is "New York".
SUMPRODUCT is like SUMIF on steroids and it allows you to automate all
these reports that you develop with data that you import from a large
centralized database, centralized accounting, manufacturing and sales
programs or the Internet.
The last argument (preceded by a comma) tells Excel what to sum and
the two previous arguments (separated by an asterisk) tell Excel about
the conditions for summing.
You can do this with SUMPRODUCT and much more.
Within a table where months are in column "A" and cities in row "11"
the formula would read like this
=SUMPRODUCT((Data1!$B$1:$B$500=$A13)*(Data1!$C$1:$C$500=B$11),(Data1!$E
$1:$E$500))
Visit: http://www.excel-examples.com/00-excel-function-sumproduct.htm
Rediscover Excel and enjoy