Can i use SumIf with multiple criteria

[thunder_anger]

Hi there

i have here this table
<table width="331" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="2"> <col style="width: 56pt;" width="75"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">Item</td> <td class="xl65" style="width: 48pt;" width="64">Amount</td> <td class="xl65" style="width: 56pt;" width="75">Date</td> <td class="xl65" style="width: 48pt;" width="64">Store</td> <td class="xl65" style="width: 48pt;" width="64">Ref.</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">102255</td> <td class="xl66" style="border-left: medium none;">525</td> <td class="xl67" style="border-left: medium none;">01/09/2010</td> <td class="xl66" style="border-left: medium none;">A</td> <td class="xl66" style="border-left: medium none;">Jan</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">102258</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">566</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/09/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Jan</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">102255</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">955</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">02/09/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jan</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">102255</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">1225</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">08/09/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">98855</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">25</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">12/09/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">102255</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">544</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">12/09/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">102258</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">966</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/10/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Jan</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">85555</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">96.5</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/10/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">921336</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">785</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">01/10/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">144587</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">1225</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">06/10/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Tos</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">966654</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">5252</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">23/10/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">C</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Yat</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; border-top: medium none;" height="20">988574</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">987</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">01/11/2010</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">D</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">Tos</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; border-top: medium none;" height="20">25633</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">1254</td> <td class="xl67" style="border-top: medium none; border-left: medium none;">02/11/2010</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">Tos</td> </tr> </tbody></table>
can i sum the amounts of Item "102255" that in store "A" and Ref "Jan" and date from 1 to 30/9/2010

IS that Possible

the above example is a sample of about 125,588 records

 

[RoryA]

You can't use SUMIF but you can use SUMPRODUCT, or SUMIFS if you have Excel 2007 or later.

 

[thunder_anger]

ok, thanks for the tip

but i did not use this formula Before so how will it be??
and if you please! explain it to me

 

[RoryA]

Which version of Excel?

 

[thunder_anger]

using Excel 2007 and 2010

 

[thunder_anger]

any Help

 

[RoryA]

I do have other things to do you know - this is not my job.

 

[thunder_anger]

Because your "job" is not helping people i did it my self with little hard trying. but i discovered that your hard reply to me motivated me to discover it myself. after all thanks

=SUMPRODUCT((A2:A14=G2)*(C2:C14=G3)*(D2:D14=G4);(B2:B14))

 

[T. Valko]

Because your "job" is not helping people i did it my self with little hard trying. but i discovered that your hard reply to me motivated me to discover it myself. after all thanks

=SUMPRODUCT((A2:A14=G2)*(C2:C14=G3)*(D2:D14=G4);(B2:B14))

Here's the equivalent SUMIFS formula for Excel 2007 and later.

=SUMIFS(B2:B14;A2:A14;G2;C2:C14;G3;D2:D14;G4)