Sumproduct

[TORPIDO]

Hi,

i'm only writing this after searching for an answer...

i have named column F and G "STS" and "CT" across workbook (3 SHEETS, MOB!TIAN:$F:$F, MOB!TIAN:$G:$G )

i want to count number of "NTB" in CT if STS is "Approved"

i use below formula:

=SUMPRODUCT(--(MOB!CT="NTB"),--(MOB!STS="APPROVED"))

it end up in #value

any thoughts?

 

[Special-K99]

This seems to work

=SUMPRODUCT(--(STS="NTB"),--(CT="Approved"))

Looks like you dont have to specify the worksheet name

UPDATE: Specifying the sheetname still returns a correct value.
Therefore the problem must lie with your data.
Maybe you have spaces after "Approved" ?

Split the SUMPRODUCT into two formulas so you can test both are working independently.

 

[TORPIDO]

this will sum all sheets, i'm want to sum up one sheet

 

[jorismoerings]

Hi,

The named range is related to range on a particular worksheet and by choice available throughout the workbook or just on the mentioned worksheet.
So your sumproduct formula will only calculate the result of the ranges in the sheet the ranges are related to, so this won't sum all sheets.
What 's your purpose of creating equal named ranges on 3 worksheets; do you want to count the number of "NTB" in CT if STS is "Approved" across the 3 worksheets with 1 formula?

 

[TORPIDO]

i have three sheets for my three staffs and a master sheet(within same book) to track their performance.

these three sheet contains same data...

so in master sheet i want to know how many NTB has each done; instead of typing the range i want to write range names.

 

[Dave Patton]

Ensure the scope of the individual names is set to the applicable sheet; see Formulas Name Manager.
Do not use full columns in the ranges; use a relevant number that considers your data.

In the example (see below), I have two sheets 2a and 2aa

Excel 2010
	A
8	40
9	50
1c

Cell Formulas
Range	Formula
A8	=SUMPRODUCT(--('2a'!rA="A"),('2a'!rB))
A9	=SUMPRODUCT(--('2aa'!rA="A"),('2aa'!rB))

Named Ranges
Name	Refers To	Cells
'2a'!rA	='2a'!$I$5:$I$7
'2aa'!rA	='2aa'!$I$5:$I$7
'2a'!rB	='2a'!$J$5:$J$7
'2aa'!rB	='2aa'!$J$5:$J$7

 

[Dave Patton]

Indirect will work to specify the relevant sheet.
An example of the syntax follows.

Excel 2010
	G	H
8	2a	40
9	2aa	50
10
1c

Cell Formulas
Range	Formula
H8	=SUMPRODUCT(--(INDIRECT("'"&G8&"'!rA")="A"),(INDIRECT("'"&G8&"'!rB")))
H9	=SUMPRODUCT(--(INDIRECT("'"&G9&"'!rA")="A"),(INDIRECT("'"&G9&"'!rB")))

 

[TORPIDO]

in your above example names are given individually to sheets; i'm naming same range in multiple sheets.

like

STS
MOB:TIAN!$G$2:$G$44
CT
MOB:TIAN!$F$2:$F$44

and when formula is entered the sheet name changes to workbook name

like below

=SUMPRODUCT(--(MOB!ct=1),--(MOB!sts="APPROVED"))
changes to:
=SUMPRODUCT(--('branch stats scbv.03.xlsx'!CT=1),--('branch stats scbv.03.xlsx'!STS="APPROVED"))

 

[Dave Patton]

For the formulas to work, the Range names must apply to individual sheets.
See my message
"Ensure the scope of the individual names is set to the applicable sheet; see Formulas Name Manager."

When you first define a name, it defaults to Workbook, to correct this
- delete the name
- add the name again and specify the sheet

 

[Dave Patton]

Excel 2010
	G	H
11		2
12	MOB	2
1c

Cell Formulas
Range	Formula
H11	=SUMPRODUCT(--(MOB!CT=1),--(MOB!STS="APPROVED"))
H12	=SUMPRODUCT(--(INDIRECT("'"&G12&"'!CT")=1),--(INDIRECT("'"&G12&"'!STS")="Approved"))

Named Ranges
Name	Refers To	Cells
MOB!CT	=MOB!$F$2:$F$44
MOB!STS	=MOB!$G$2:$G$44

N.B. This post uses the revised HTML Maker.