Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct ... Maybe? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions |