Sumproduct, Validation & Blancs
 

I have the following categories

Monitor-CRT = asstype
17 inch = assdescr
1 = age

formuala reads
=sumproduct(--(asstype=selection1),--(assdescr=selection2),--(age=1),

I'm using a validation list to select the criteria needed
Monitor-CRT & 17 Inch - this works fine with correct calculation

but if I only want to search on asstype and the assdescr is blank it doesn't
return the correct total because in the data the assdescr doesn't have any
blank fields.
How can I have the formuala in the one cell?

Suggestions?

TIA
Bec

Sumproduct, Validation & Blancs
 

Is this what you mean

=IF(selection2="",sumproduct(--(asstype=selection1),--(age=1),sumproduct(--(asstype=selection1),--(assdescr=selection2),--(age=1))

--
__________________________________
HTH

Bob

"Bec" wrote in message
...
I have the following categories

Monitor-CRT = asstype
17 inch = assdescr
1 = age

formuala reads
=sumproduct(--(asstype=selection1),--(assdescr=selection2),--(age=1),

I'm using a validation list to select the criteria needed
Monitor-CRT & 17 Inch - this works fine with correct calculation

but if I only want to search on asstype and the assdescr is blank it
doesn't
return the correct total because in the data the assdescr doesn't have any
blank fields.
How can I have the formuala in the one cell?

Suggestions?

TIA
Bec

Sumproduct, Validation & Blancs
 

What if selection2 isn't blank?

"Bob Phillips" wrote:

Is this what you mean

=IF(selection2="",sumproduct(--(asstype=selection1),--(age=1),sumproduct(--(asstype=selection1),--(assdescr=selection2),--(age=1))

--
__________________________________
HTH

Bob

"Bec" wrote in message
...
I have the following categories

Monitor-CRT = asstype
17 inch = assdescr
1 = age

formuala reads
=sumproduct(--(asstype=selection1),--(assdescr=selection2),--(age=1),

I'm using a validation list to select the criteria needed
Monitor-CRT & 17 Inch - this works fine with correct calculation

but if I only want to search on asstype and the assdescr is blank it
doesn't
return the correct total because in the data the assdescr doesn't have any
blank fields.
How can I have the formuala in the one cell?

Suggestions?

TIA
Bec