sumproduct

  • Thread starter Thread starter brianwa
  • Start date Start date
B

brianwa

Is there something I am missing in this formula?

=SUMPRODUCT((Sheet2!$C$3:$C$50000=C$6)*(Sheet2!$A$3:$A$50000=$A7),Sheet2!$D$3:$D$50000)

c6 & a7 contain the values of which I want to sum up on sheet 2.

I've double checked the data and it is all there but I continue to get
a value of 0!

Please help....

Thanks in advance
BW
 
Hi
what kind of values are in column C, A. Some ideas:
- you're checking for numbers but the column is formated as 'Text'
- You are comparing dates with date+time values
 
Column C contains certain order codes like TA, ZCO ect
Column A contains numbers which represent the month ie 1 for Jan, 2 fo
feb.

A7 contains the same number that would appear in Sheet 2 column A

C6 contains the order code that I want to lookup, in this case "TA".

I've formatted the column A to represent numbers but the formula stil
doesn't work.

B
 
BW,

Try and home in on the error,

Put the c6 value in C3, the A7 value in A3, and say 10 in D3. You should at
least get 10 back then. Try and see what you get.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
try the following
- copy an empty cell with CTRL+C
- select column A
- goto 'Edit - Paste Special' and choose the action 'Add'

Test your formula again
 
I found it. Even though the order codes were typed exactly the same i
wasn't picking it up. I copied each particular order code from Sheet
and pasted it C6, C7 so forth. Strangest thing I've seen from Exce
yet!

Thanks for all your help guys!

B
 
Well that shows that the formula works, so you need to check the data and
see that what is in A is the same as A7, C in C6, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top