|
Combine 2 formulas
Hello from Steved
Please how do I combine the below to give me a total in 1 cell. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Thankyou. |
Hello from Steved
I've got the result Thankyou. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898)+SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) "Steved" wrote: Hello from Steved Please how do I combine the below to give me a total in 1 cell. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Thankyou. |
=SUMPRODUCT(--ISNUMBER(MATCH('From
Charters'!$A$1:$A$898,{"Papakura","Wiri"},0)),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Steved wrote: Hello from Steved I've got the result Thankyou. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898)+SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) "Steved" wrote: Hello from Steved Please how do I combine the below to give me a total in 1 cell. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Thankyou. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Hello Aladin form Steved
I would like to say thankyou verymuch as I like clean formulas such as yours. Cheers "Aladin Akyurek" wrote: =SUMPRODUCT(--ISNUMBER(MATCH('From Charters'!$A$1:$A$898,{"Papakura","Wiri"},0)),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Steved wrote: Hello from Steved I've got the result Thankyou. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898)+SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) "Steved" wrote: Hello from Steved Please how do I combine the below to give me a total in 1 cell. =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Papakura"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) =SUMPRODUCT(--('From Charters'!$A$1:$A$898="Wiri"),--('From Charters'!$B$1:$B$898="Period.4"),'From Charters'!$D$1:$D$898) Thankyou. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| All times are GMT +1. The time now is 08:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com