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 |
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 |