Join two formulas

[lunker55]

I have this formula:
=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--(MONTH('2003-2004'!$A
$4:$A$10000)=2),'2003-2004'!$E$4:$E$10000)

And I only want this to be true if:


{=SUM((builder="m")*(servicedby="Dave")*nc)}

I don't think it is as easy as joining two together, but I tried:

=IF(((builder="m")*(servicedby="Dave")),(SUMPRODUCT(--(YEAR('2003-2004'!$A$4
:$A$10000)=2004,--(MONTH('2003-2004'!$A$4:$A$10000)=2),('2003-2004'!$E$4:$E$
10000))),1))

I also tried:

=SUMPRODUCT(--(YEAR('2003-2004'!$A$4:$A$10000)=2004),--(MONTH('2003-2004'!$A
$4:$A$10000)=2),--(TEXT('2003-2004'!$A$4:$A$10000)="Dave"),'2003-2004'!$E$4:
$E$10000)

My ideas are better than my knowledge of Excel!
Better buy a third book.

Joe

 

[Jason Morin]

Correct me if I'm wrong, but it looks like you want to sum
E4:E10000 where:

1. The date in A4:A10000 falls during Feb. 2004
2. The named range "builder" equals "m"
3. The named range "servicedby" equals "Dave".

Assuming your named ranges are of the same dimension
(size) as E4:E10000, then try:

=SUMPRODUCT((YEAR('2003-2004'!$A$4:$A$10000)=2004)*(MONTH
('2003-2004'!$A$4:$A$10000)=2)*(builder="m")*
(servicedby="Dave")*'2003-2004'!$E$4:$E$10000)

HTH
Jason
Atlanta, GA

 

[Frank Kabel]

Hi
how are your both names 'builder' and 'servicedby'
defined. That is which column do they represent. After
this it should be quite easy.

 

[lunker55]

Jason: I get an #N/A error.

Frank:
My "builder" name is just a named range B4:B10000
And my "servicedby" range is L4:L10000

Joe

 

[lunker55]

I think it is working now.
I had my "builder" and "servicedby" ranged a different lenght from the other
ranges.

Joe

 

[lunker55]

Thanks Jason and Frank.
Works perfectly!

Joe

I think it is working now.
I had my "builder" and "servicedby" ranged a different lenght from the other
ranges.

Joe