Multiple Conditional Sum Help
I am pulling information off two different documents to try and creat
conditional values in the current spreadshet(Daily Store Totals). I am trying to pull information off of the Daily Customer Totals. The sheet is a continuous listing by date of all information: A B C 1 DATE STORE CODE Customer 2 5/30/09 601 John Smith 3 6/1/09 813 Joe Black 4 6/1/09 724 Jim Chang 5 6/1/09 619 Jerry Fiest 6 6/1/09 813 Steven Calhoon I am trying to count how many applications were submited by the store code on each day. So I am trying to make a conditional function in the new spreadsheet: A B C D E 1 Date | 6/1/09 | 2 Store Code | Total App Rec | Den | Pen | Apr | 3 601 =Function? 4 619 5 724 6 813 I am running 2003 excell and cant wrap my head around how to format the function, i was trying to do =IF('[Daily Customer Totals]Sheet 1'!$B:$B=A3,IF('[Daily Customer Totals]Sheet 1'!$A:$A=B1, SUMIF('[Daily Customer Totals]Sheet 1'!$B:$B, A3, .......... or somthing along those lines. Please help! |
Multiple Conditional Sum Help
You'll want to use SUMPRODUCT for multiple criteria checks. I believe you need
=SUMPRODUCT(('[Daily Customer Totals]Sheet 1'!$B$2:$B$100=$A3)*('[Daily Customer Totals]Sheet 1'!$A$2:$A$100=B$1)) SUMPRODUCT allows you to multiply arrays against each other, so your final sum/count will only include conditions where TRUE*TRUE (1*1) exists. Note that with pre-2007 XL, SUMPRODUCT can not reference an entire column. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Artamas" wrote: I am pulling information off two different documents to try and creat conditional values in the current spreadshet(Daily Store Totals). I am trying to pull information off of the Daily Customer Totals. The sheet is a continuous listing by date of all information: A B C 1 DATE STORE CODE Customer 2 5/30/09 601 John Smith 3 6/1/09 813 Joe Black 4 6/1/09 724 Jim Chang 5 6/1/09 619 Jerry Fiest 6 6/1/09 813 Steven Calhoon I am trying to count how many applications were submited by the store code on each day. So I am trying to make a conditional function in the new spreadsheet: A B C D E 1 Date | 6/1/09 | 2 Store Code | Total App Rec | Den | Pen | Apr | 3 601 =Function? 4 619 5 724 6 813 I am running 2003 excell and cant wrap my head around how to format the function, i was trying to do =IF('[Daily Customer Totals]Sheet 1'!$B:$B=A3,IF('[Daily Customer Totals]Sheet 1'!$A:$A=B1, SUMIF('[Daily Customer Totals]Sheet 1'!$B:$B, A3, .......... or somthing along those lines. Please help! |
Multiple Conditional Sum Help
PIVOT table is an easy way to analyze your data
"Artamas" wrote: I am pulling information off two different documents to try and creat conditional values in the current spreadshet(Daily Store Totals). I am trying to pull information off of the Daily Customer Totals. The sheet is a continuous listing by date of all information: A B C 1 DATE STORE CODE Customer 2 5/30/09 601 John Smith 3 6/1/09 813 Joe Black 4 6/1/09 724 Jim Chang 5 6/1/09 619 Jerry Fiest 6 6/1/09 813 Steven Calhoon I am trying to count how many applications were submited by the store code on each day. So I am trying to make a conditional function in the new spreadsheet: A B C D E 1 Date | 6/1/09 | 2 Store Code | Total App Rec | Den | Pen | Apr | 3 601 =Function? 4 619 5 724 6 813 I am running 2003 excell and cant wrap my head around how to format the function, i was trying to do =IF('[Daily Customer Totals]Sheet 1'!$B:$B=A3,IF('[Daily Customer Totals]Sheet 1'!$A:$A=B1, SUMIF('[Daily Customer Totals]Sheet 1'!$B:$B, A3, .......... or somthing along those lines. Please help! |
Multiple Conditional Sum Help
Here is the link
http://www.4shared.com/file/11556675...ca57/Data.html "Teethless mama" wrote: PIVOT table is an easy way to analyze your data "Artamas" wrote: I am pulling information off two different documents to try and creat conditional values in the current spreadshet(Daily Store Totals). I am trying to pull information off of the Daily Customer Totals. The sheet is a continuous listing by date of all information: A B C 1 DATE STORE CODE Customer 2 5/30/09 601 John Smith 3 6/1/09 813 Joe Black 4 6/1/09 724 Jim Chang 5 6/1/09 619 Jerry Fiest 6 6/1/09 813 Steven Calhoon I am trying to count how many applications were submited by the store code on each day. So I am trying to make a conditional function in the new spreadsheet: A B C D E 1 Date | 6/1/09 | 2 Store Code | Total App Rec | Den | Pen | Apr | 3 601 =Function? 4 619 5 724 6 813 I am running 2003 excell and cant wrap my head around how to format the function, i was trying to do =IF('[Daily Customer Totals]Sheet 1'!$B:$B=A3,IF('[Daily Customer Totals]Sheet 1'!$A:$A=B1, SUMIF('[Daily Customer Totals]Sheet 1'!$B:$B, A3, .......... or somthing along those lines. Please help! |
Multiple Conditional Sum Help
Wonderful! It works perfectly! <3
"Luke M" wrote: You'll want to use SUMPRODUCT for multiple criteria checks. I believe you need =SUMPRODUCT(('[Daily Customer Totals]Sheet 1'!$B$2:$B$100=$A3)*('[Daily Customer Totals]Sheet 1'!$A$2:$A$100=B$1)) SUMPRODUCT allows you to multiply arrays against each other, so your final sum/count will only include conditions where TRUE*TRUE (1*1) exists. Note that with pre-2007 XL, SUMPRODUCT can not reference an entire column. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Artamas" wrote: I am pulling information off two different documents to try and creat conditional values in the current spreadshet(Daily Store Totals). I am trying to pull information off of the Daily Customer Totals. The sheet is a continuous listing by date of all information: A B C 1 DATE STORE CODE Customer 2 5/30/09 601 John Smith 3 6/1/09 813 Joe Black 4 6/1/09 724 Jim Chang 5 6/1/09 619 Jerry Fiest 6 6/1/09 813 Steven Calhoon I am trying to count how many applications were submited by the store code on each day. So I am trying to make a conditional function in the new spreadsheet: A B C D E 1 Date | 6/1/09 | 2 Store Code | Total App Rec | Den | Pen | Apr | 3 601 =Function? 4 619 5 724 6 813 I am running 2003 excell and cant wrap my head around how to format the function, i was trying to do =IF('[Daily Customer Totals]Sheet 1'!$B:$B=A3,IF('[Daily Customer Totals]Sheet 1'!$A:$A=B1, SUMIF('[Daily Customer Totals]Sheet 1'!$B:$B, A3, .......... or somthing along those lines. Please help! |
All times are GMT +1. The time now is 08:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com