Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
I have data comming in to an excel file from Access. Once in excel the
spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
are you sure there are no leading/trailing spaces in the data? you could use
TRIM to remove them (you could also use text to ensure the comparison is on a text basis): =SUMPRODUCT(--(TEXT(TRIM(A2:A7),"0000")="9188"),--(TRIM(B2:B7)="T")) "Dan" wrote: I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
I think I got it I removed the ,from the ),--(B2.... so the formula is now:
=SUMPRODUCT(--(A2:A7="9188")--(B2:B7="T")) why do I now get a 9 when I reference another sheet? =SUMPRODUCT(--(A2:A7=Sheet1!H5)--(B2:B7="T")) Thanks again "Dan" wrote: I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
I would bet that the Access data has leading or trailing spaces, so use
=SUMPRODUCT(--(TRIM(A2:A7)="9188"),--(TRIM(B2:B7)="T")) The last two fail because you cannot use whole columns in SUMPRODUCT, only a specific range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dan" wrote in message ... I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
No leading or trailing spaces the field is only 4 characters. I removed the
comma so the line reads: =SUMPRODUCT(--(TRIM(A2:A7)="9188")--(TRIM(B2:B7)="T")) which works but when I substitute the "9188" with a cell reference I get an answer of 9. I think its multipling the number of 9188 with the number if T which would be 3*3 "Bob Phillips" wrote: I would bet that the Access data has leading or trailing spaces, so use =SUMPRODUCT(--(TRIM(A2:A7)="9188"),--(TRIM(B2:B7)="T")) The last two fail because you cannot use whole columns in SUMPRODUCT, only a specific range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dan" wrote in message ... I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
You shouldn't remove the , that is totally wrong. Can you lay some example
data out and tell us what you get with it, this seems silly the results you are getting. BTW is 9188 really text, or numbers? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dan" wrote in message ... No leading or trailing spaces the field is only 4 characters. I removed the comma so the line reads: =SUMPRODUCT(--(TRIM(A2:A7)="9188")--(TRIM(B2:B7)="T")) which works but when I substitute the "9188" with a cell reference I get an answer of 9. I think its multipling the number of 9188 with the number if T which would be 3*3 "Bob Phillips" wrote: I would bet that the Access data has leading or trailing spaces, so use =SUMPRODUCT(--(TRIM(A2:A7)="9188"),--(TRIM(B2:B7)="T")) The last two fail because you cannot use whole columns in SUMPRODUCT, only a specific range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dan" wrote in message ... I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT Formula
try in a cell
=trim(a2)="9188" in another =trim(b5)="T" I think you will find that the first cell read false and the second cell reads true by removing the comma, you are effectively using an eqaution similar to =SUMPRODUCT(--(TRIM(A2:A7)="9188"))+sumproduct(--(TRIM(B2:B7)="T")) your equation is counting tha number of times trim(A2... and Trim(b2... are true indvidually. if it responds 3 it indicates there are only three trues in the 12 possible true false possibilities. if it responds 9 there are 9 trues. the cell reference you used did the cell have 9188 in it? "Dan" wrote: No leading or trailing spaces the field is only 4 characters. I removed the comma so the line reads: =SUMPRODUCT(--(TRIM(A2:A7)="9188")--(TRIM(B2:B7)="T")) which works but when I substitute the "9188" with a cell reference I get an answer of 9. I think its multipling the number of 9188 with the number if T which would be 3*3 "Bob Phillips" wrote: I would bet that the Access data has leading or trailing spaces, so use =SUMPRODUCT(--(TRIM(A2:A7)="9188"),--(TRIM(B2:B7)="T")) The last two fail because you cannot use whole columns in SUMPRODUCT, only a specific range. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dan" wrote in message ... I have data comming in to an excel file from Access. Once in excel the spreadsheet does it's thing providing minor breakdowns with various countif's, sumif's and graphs etc. I am new to the Sumproduct, the following is a sample of the data. I am, in this case looking for the number of 9188 and is T class. I have tried the formulas with and without quotes around 9188: =SUMPRODUCT(--(A2:A7="9188"),--(B2:B7="T")) =SUMPRODUCT(A2:A7="9188")*(B2:B7="T") =SUMPRODUCT(--(A2:A64000="9188"),--(B2:B64000="T")) =SUMPRODUCT(A2:A64000="9188")*(B2:B64000="T") =SUMPRODUCT(--(A:A="9188"),--(B:B="T")) result #NUM =SUMPRODUCT(A:A="9188")*(B:B="T") result #NUM A B 1 Number Class 2 9188 S 3 9188 D 4 9188 Z 5 9188 T 6 9188 T 7 9188 T The first four give me a result of 0 instead of 3 and I think I understand the #NUM error. Cell formats are "General", Number field in Access is text if that helps(some numbers start with 0 which is why text). If there is another way of going about this using a countif(AND( or something else I am open to suggestions. Very much Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct or Other Formula? | Excel Worksheet Functions | |||
OR in a SUMPRODUCT formula | Excel Worksheet Functions | |||
SumProduct Formula Help | Excel Worksheet Functions | |||
SumProduct Formula | Excel Worksheet Functions | |||
Sumproduct Formula | Excel Worksheet Functions |