Sumif as a fuction of If using multiple criteria
 

Hello,

I am trying to create a formula to sum total units sold as a function of a
list of zip codes and product codes. I have 30,000 lines of sales data where
the 1st column is Zip Code, 2nd column is Item Code, 3rd is Units Sold. I
need to sum the Units Sold for a list of national zip codes only if they
match a list of 520 Product Codes.

The formula I have tried which does NOT work is as follows:

=IF(A2='Sales Data'!$A$2:$A$30000,SUMIF('Sales Data'!$B$2:$B$30000,'Item
Codes'!$A$2:$A$520,'Sales Data'!$C$2:$C$30000),FALSE)

Please let me know what other information you need to assist me. I
appreciate the help anyone out there can provide.

Regards,
--
Adam B
National Business Analyst

Sumif as a fuction of If using multiple criteria
 

Modify this idea to suit your needs. It is looking for a G match in H to
sum I
=SUMPRODUCT(--(ISNUMBER(MATCH(H2:H11,G2:G11,0))),I2:I11)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AMB" wrote in message
...
Hello,

I am trying to create a formula to sum total units sold as a function of a
list of zip codes and product codes. I have 30,000 lines of sales data
where
the 1st column is Zip Code, 2nd column is Item Code, 3rd is Units Sold. I
need to sum the Units Sold for a list of national zip codes only if they
match a list of 520 Product Codes.

The formula I have tried which does NOT work is as follows:

=IF(A2='Sales Data'!$A$2:$A$30000,SUMIF('Sales Data'!$B$2:$B$30000,'Item
Codes'!$A$2:$A$520,'Sales Data'!$C$2:$C$30000),FALSE)

Please let me know what other information you need to assist me. I
appreciate the help anyone out there can provide.

Regards,
--
Adam B
National Business Analyst

Sumif as a fuction of If using multiple criteria
 

Maybe this:

=SUMPRODUCT(--('Sales Data'!$A$2:$A$30000=A2),--(ISNUMBER(MATCH('Sales
Data'!$B$2:$B$30000,'Item Codes'!$A$2:$A$520,0))),'Sales
Data'!$C$2:$C$30000)

HTH,
Paul

--

"AMB" wrote in message
...
Hello,

I am trying to create a formula to sum total units sold as a function of a
list of zip codes and product codes. I have 30,000 lines of sales data
where
the 1st column is Zip Code, 2nd column is Item Code, 3rd is Units Sold. I
need to sum the Units Sold for a list of national zip codes only if they
match a list of 520 Product Codes.

The formula I have tried which does NOT work is as follows:

=IF(A2='Sales Data'!$A$2:$A$30000,SUMIF('Sales Data'!$B$2:$B$30000,'Item
Codes'!$A$2:$A$520,'Sales Data'!$C$2:$C$30000),FALSE)

Please let me know what other information you need to assist me. I
appreciate the help anyone out there can provide.

Regards,
--
Adam B
National Business Analyst