SUMPRODUCT
 

Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

Is the A5 on Sheet1?
Have you tried part-formulas, like
a)=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5))
b)=SUMPRODUCT(--('Data from MIS'!N4:N65535="Not Moderated"))
to see if those parts work?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Alex H" wrote in message
...
Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

There doesn't appear to be anything wrong with your formula. I would suspect
the problem lies with your cell formatting in cell A5 and Column M. Perhaps
one contains Text and the other Numbers?

To Excel, the text value 123 and the number 123 are different, even though
they may look the same when displayed in the cell.

Try changing the format of cell A5, then re-entering the value. See if that
helps.

HTH,
Elkar


"Alex H" wrote:

Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

Hi and thanks for your help

Sheet1!A5 is formatted custom mmm-yy and is Data from MIS!Column M


"Elkar" wrote in message
...
There doesn't appear to be anything wrong with your formula. I would
suspect
the problem lies with your cell formatting in cell A5 and Column M.
Perhaps
one contains Text and the other Numbers?

To Excel, the text value 123 and the number 123 are different, even though
they may look the same when displayed in the cell.

Try changing the format of cell A5, then re-entering the value. See if
that
helps.

HTH,
Elkar


"Alex H" wrote:

Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

Hi Bernard, and thanks for your post

yes i have tested bioth parts and it is a0 that is not working in that it is
returning a 0. However i have checked that that both Sheet1!A5i s formatted
Custom mmm-yy and Data for MIS Column M is also formatted custom m mm-yy

Part b works and is returning correct number

A


"Bernard Liengme" wrote in message
...
Is the A5 on Sheet1?
Have you tried part-formulas, like
a)=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5))
b)=SUMPRODUCT(--('Data from MIS'!N4:N65535="Not Moderated"))
to see if those parts work?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Alex H" wrote in message
...
Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

Ok. Dates in Excel are stored as serial numbers. For example, today's date
(9/7/2007) is actually stored as 39332. The cell formatting causes Excel to
display 39332 as a recognizable date, but the underlying value remains the
same. Since you said your format is mmm-yy, all dates in September will
appear to be the same, but they may not be, if the DAY is different. The
formula uses the underlying values (serial numbers), not necessarily what is
displayed due to cell formatting.

Now, with all that being said, let's try this with your formula:

=SUMPRODUCT(--(TEXT('Data from
MIS'!M4:M65535,"mmm-yy")=TEXT(A5,"mmm-yy")),--('Data from MIS'!N4:N65535="Not
Moderated"),'Data from MIS'!P4:P65535)

By using the TEXT functions, we are effectively comparing the displayed
dates rather than the underlying serial numbers.

HTH,
Elkar


"Alex H" wrote:

Hi and thanks for your help

Sheet1!A5 is formatted custom mmm-yy and is Data from MIS!Column M


"Elkar" wrote in message
...
There doesn't appear to be anything wrong with your formula. I would
suspect
the problem lies with your cell formatting in cell A5 and Column M.
Perhaps
one contains Text and the other Numbers?

To Excel, the text value 123 and the number 123 are different, even though
they may look the same when displayed in the cell.

Try changing the format of cell A5, then re-entering the value. See if
that
helps.

HTH,
Elkar


"Alex H" wrote:

Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A

SUMPRODUCT
 

Elkar - you are brilliant - VERY many thanks.... that has sorted it.. Of
coursed when it is explained it is easy to understand - it is having the
knowledge - Once agai n, many thanks

Alex



"Elkar" wrote in message
...
Ok. Dates in Excel are stored as serial numbers. For example, today's
date
(9/7/2007) is actually stored as 39332. The cell formatting causes Excel
to
display 39332 as a recognizable date, but the underlying value remains the
same. Since you said your format is mmm-yy, all dates in September will
appear to be the same, but they may not be, if the DAY is different. The
formula uses the underlying values (serial numbers), not necessarily what
is
displayed due to cell formatting.

Now, with all that being said, let's try this with your formula:

=SUMPRODUCT(--(TEXT('Data from
MIS'!M4:M65535,"mmm-yy")=TEXT(A5,"mmm-yy")),--('Data from
MIS'!N4:N65535="Not
Moderated"),'Data from MIS'!P4:P65535)

By using the TEXT functions, we are effectively comparing the displayed
dates rather than the underlying serial numbers.

HTH,
Elkar


"Alex H" wrote:

Hi and thanks for your help

Sheet1!A5 is formatted custom mmm-yy and is Data from MIS!Column M


"Elkar" wrote in message
...
There doesn't appear to be anything wrong with your formula. I would
suspect
the problem lies with your cell formatting in cell A5 and Column M.
Perhaps
one contains Text and the other Numbers?

To Excel, the text value 123 and the number 123 are different, even
though
they may look the same when displayed in the cell.

Try changing the format of cell A5, then re-entering the value. See if
that
helps.

HTH,
Elkar


"Alex H" wrote:

Hi could some kind eoprson help me please.

Through help from this forum i have arraived at:
=SUMPRODUCT(--('Data from MIS'!M4:M65535=A5),--('Data from
MIS'!N4:N65535="Not Moderated"),'Data from MIS'!P4:P65535)

However this is returning 0 whereas it should be returning 18 from
the
data.

What I am trying to do is in Sheet1!C5 add the values in Data from
MIS
Column P where Data from MIS!ColumnM is equal to Sheet1! A5 and Data
from
MIS!ColumnN is equal to "Not Moderated"

many thanks

A