locating data with 4 variables (2 row and 2 column)

[LJG]

I have a spreadsheet set up similar to the one attached. I have used sumproduct to be able to locate the number of flights that Pilot Alan made in the Plane Beta during March of 2013 while restricting the range to only the first 3 rows of data =sumproduct((plane="Beta")*(pilot="Alan")*(year="2013")*(month="Mar")*$e$4:$p$6). My problem comes in when I try to extend the data range to the full data set $e$4:$p$14, I receive the #N/A error. I am assuming my problem is due to the blank rows that seperate the pilots, which I need for format and user reasons. I have tried to use -- intead of the * and receive back the #VALUE! error, but I may not be using the double dash correctly. Any help or suggestions would be greatly appreciated!

 

[Somendra Misra]

Hi LJG,

Welcome to the forum.

Just an alternate solution.

Regards,

 

[LJG]

Thank you Somendra Misra!

This worked beautifully. The only thing that worries me is having to use the {} array. There will be hundreds of these on a sheet and multiple sheets. Once my not-so-savy users start to use the sheets, I'm afraid that's where things will get derailed.

 

[Somendra Misra]

@LJG

Than try :

=SUMPRODUCT($E$4:$P$14*(plane=B19)*(pilot=C19)*($E$2:$P$2=B20)*($E$3:$P$3=C20))

as in file.

Regards,

 

[NARAYANK991]

Hi ,

Your assumption is wrong ; the #N/A error is because the range definitions do not match ; if your definitions of plane and pilot are restricted to the first 3 rows , then if you try to extend the data range to P14 , you will get an error.

If you extend the range definitions for plane and pilot to row 14 , your existing formula will work correctly.

See the attached file.

Narayan