Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
na
 
Posts: n/a
Default Sumproduct #N/A elminate?

I am using the following forumla:

=SUMPRODUCT(--(Sheet2!$D$2:$D$396DATE(2004,6,15)),--(Sheet2!$D$2:$D$396<=DATE(2004,7,15)),Sheet2!$G$2: $G$396)+SUMPRODUCT(--(Sheet2!$K$2:$K$396DATE(2004,6,15)),--(Sheet2!$K$2:$K$396<=DATE(2004,7,15)),Sheet2!$N$2: $N$396)

The question is: How do I omit cells within the range that have #N/A
(because data hasn't been entered in that cell to make the function work)?

Thanks
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
easiest way would be to change the formulas which creates the #NA. what
formula are you using in these cells

--
Regards
Frank Kabel
Frankfurt, Germany


na wrote:
I am using the following forumla:


=SUMPRODUCT(--(Sheet2!$D$2:$D$396DATE(2004,6,15)),--(Sheet2!$D$2:$D$39
6<=DATE(2004,7,15)),Sheet2!$G$2:$G$396)+SUMPRODUCT (--(Sheet2!$K$2:$K$39
6DATE(2004,6,15)),--(Sheet2!$K$2:$K$396<=DATE(2004,7,15)),Sheet2!$N$2:
$N$396)

The question is: How do I omit cells within the range that have #N/A
(because data hasn't been entered in that cell to make the function
work)?

Thanks


  #3   Report Post  
na
 
Posts: n/a
Default

In the first sumproduct Column G has the following:
=E40*(VLOOKUP(F40,Airfare,2,FALSE))

in the second sum product columan N has: =VLOOKUP(M2,Airfare,3,False)

"Frank Kabel" wrote:

Hi
easiest way would be to change the formulas which creates the #NA. what
formula are you using in these cells

--
Regards
Frank Kabel
Frankfurt, Germany


na wrote:
I am using the following forumla:


=SUMPRODUCT(--(Sheet2!$D$2:$D$396DATE(2004,6,15)),--(Sheet2!$D$2:$D$39
6<=DATE(2004,7,15)),Sheet2!$G$2:$G$396)+SUMPRODUCT (--(Sheet2!$K$2:$K$39
6DATE(2004,6,15)),--(Sheet2!$K$2:$K$396<=DATE(2004,7,15)),Sheet2!$N$2:
$N$396)

The question is: How do I omit cells within the range that have #N/A
(because data hasn't been entered in that cell to make the function
work)?

Thanks



  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


na Wrote:
In the first sumproduct Column G has the following:
=E40*(VLOOKUP(F40,Airfare,2,FALSE))

in the second sum product columan N has:
=VLOOKUP(M2,Airfare,3,False)...


If Airfare is sorted on its first column, use the faster:

=E40*IF(VLOOKUP(F40,Airfare,1,1)=F40,VLOOKUP(F40,A irfare,2,1),0)

Otherwise, you have to resort to costly...

=E40*IF(ISNA(VLOOKUP(F40,Airfare,2,FALSE)),0,VLOOK UP(F40,Airfare,2,FALSE))


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274352

  #5   Report Post  
na
 
Posts: n/a
Default

Thanks - the second one worked for me!

"Aladin Akyurek" wrote:


na Wrote:
In the first sumproduct Column G has the following:
=E40*(VLOOKUP(F40,Airfare,2,FALSE))

in the second sum product columan N has:
=VLOOKUP(M2,Airfare,3,False)...


If Airfare is sorted on its first column, use the faster:

=E40*IF(VLOOKUP(F40,Airfare,1,1)=F40,VLOOKUP(F40,A irfare,2,1),0)

Otherwise, you have to resort to costly...

=E40*IF(ISNA(VLOOKUP(F40,Airfare,2,FALSE)),0,VLOOK UP(F40,Airfare,2,FALSE))


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=274352


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct on filtered cells Ndel40 Excel Worksheet Functions 19 January 20th 05 11:17 PM
SUMPRODUCT Problem Mestrella31 Excel Discussion (Misc queries) 2 December 21st 04 08:01 PM
Sumproduct ... Empty Cells vs Spaces? Ken Excel Discussion (Misc queries) 9 December 17th 04 09:03 PM
Shorten sumproduct formula Andre Croteau Excel Discussion (Misc queries) 1 December 11th 04 11:30 PM
Optimize SumProduct Christopher Kennedy Excel Discussion (Misc queries) 9 December 10th 04 05:47 PM


All times are GMT +1. The time now is 11:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"