Which formula to use?

[Cresta]

Hello
I have a grid of data, down the left is 2 columns for product item and
description, along the top are months. I need a formula function to search
along the top to find the correct month, then search down the 1st column to
find a product match, then search down the 2nd column to find a description
match. The intersection of the correct row(s) and column being the value i
require. There may be several returns matching the same criteria, so the
results need adding together.
Previously (xl2003) we used a sum(if array but it is slowing the
calculations right down. Now (xl2007) I have looked into the new sumifs and
getpivotdata but can't get any sense out of either. I can do a custom
function, but at the moment would rather keep it standard.
Any ideas
Thanks

 

[Mike H]

Hi,

If I understand correctly you have a table similar to this

Jan Feb Mar Apr
a b 1 2 3 4
c d 2 3 4 5
e f 8 7 6 5
a b 4 5 6 7

I've limited this to 4 months to prevent it wrapping
=SUMPRODUCT((A2:A5=N1)*(B2:B5=N2)*(C1:F1=N3)*(C2:F5))

The above formula will return 7 where:
N1= a
N2= b
N3= Feb
Note That all the dates are correctly formatted dates and not simply text

Mike

 

[Cresta]

Thanks Mike, I have successfully used your example below for the right
results.(Your datagrid is exactly how it is, well deciphered).

I have also managed the sumifs by way of additional formula,
=SUMIFS(INDIRECT(ADDRESS(2,(MATCH($I$1,Headers)),,,)&":"&ADDRESS(9,(MATCH($I$1,Headers)))),A1:A9,"=c",B2:B9,"=c")

We have a massive workbook (xl03) with many sum(if arrays, which takes about
8 minutes to caluculate on a modern pc. We have moved it to xl07 and are
trying to speed it up by replacing the old arrays with more efficient
formula. Which of the two examples above would run the quickest?

Thanks again

 

[Mike H]

Cresta,

Sumifs is an Excel 2007 function and as a poor pensioner of modest means I
haven't upgraded to so I can't be certain but if I look at the amount of
calls in the sumifs option it's hard to believe it won't be slower.

I'm glad my first solution worked.

Mike