SUMPRODUCT

[Alex H]

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'!P465535)

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

 

[Bernard Liengme]

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

 

[Elkar]

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]

Hi and thanks for your help

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

 

[Alex H]

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

 

[Elkar]

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'!P465535)

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

HTH,
Elkar

 

[Alex H]

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