Jul 29 2024 08:50 AM
Jul 29 2024 10:06 AM
Perhaps you mean something like
=SUM(
SUMPRODUCT(
(HX!DV2:DV63590 = 18)*
(HX!Q2:Q63590 = "0735")*
(
(--HX!P2:P63590 = 94001) +
(--HX!P2:P63590 = 91142) +
(--HX!P2:P63590 = 94004)
) *
(
(HX!EN2:EN63590 = "5500") +
(HX!EO2:EO63590 = "5500") +
(HX!EP2:EP63590 = "5500") +
(HX!EQ2:EQ63590 = "5500")
) *
--HX!BC2:BC63590 )
)
In any case you need to start from the second row and check where are numbers and where are texts which looks like numbers.
Your sample file has no HX sheet and I'm not sure columns in Sheet1 are mapped on ones used in formula. Thus above is just my guess.
Jul 29 2024 10:29 AM - edited Jul 29 2024 10:32 AM
As Excel shows us (in my version), you have an extra right-parenthesis at the end. Also, the use of SUM and SUMPRODUCT is redundant. Choose one of the following:
=SUM((HX!DV1:DV63590 = 18) * (HX!Q1:Q63590 = "0735")
* ((HX!P1:P63590 = 94001) + (HX!P1:P63590 = 91142)
+ (HX!P1:P63590 = 94004))
* ((HX!EN1:EN63590 = "5500") + (HX!EO1:EO63590 = "5500")
+ (HX!EP1:EP63590 = "5500") + (HX!EQ1:EQ63590 = "5500"))
* HX!BC1:BC63590)
or
=SUMPRODUCT((HX!DV1:DV63590 = 18) * (HX!Q1:Q63590 = "0735")
* ((HX!P1:P63590 = 94001) + (HX!P1:P63590 = 91142)
+ (HX!P1:P63590 = 94004))
* ((HX!EN1:EN63590 = "5500") + (HX!EO1:EO63590 = "5500")
+ (HX!EP1:EP63590 = "5500") + (HX!EQ1:EQ63590 = "5500"))
* HX!BC1:BC63590)
The SUM version works in versions of Excel that are dynamic-array aware.
The SUMPRODUCT version works in all versions of Excel.
I don't know why you provided the Excel data file. If you want help with debugging your logic, you need to provide a file with data in the appropriate columns. And you should highlight rows where you expect the logic to be true. I don't see any column with 18 in the highlighted rows in your file.
Jul 29 2024 10:31 AM
Thank you for your prompt response.
I apologize for not including the correct sheet name in the previous file. I have now updated the file to reflect the correct sheet name, which is HX, and the formulas are adjusted accordingly. I applied your formula to the Formula Test tab; however, it returns zero, which does not match the expected result. According to the sample data, the correct result should be -6,662.62. Additionally, I have tried another formula, which is also returning zero which is labeled Regina.
Could you please provide further assistance to resolve this issue? Any additional guidance or suggestions would be greatly appreciated.
Thank you for your help.
Best regards,
Regina
Jul 29 2024 10:39 AM
Thank you for your help.
I apologize for not including the correct sheet name in the previous file. I have now updated the file to reflect the correct sheet name, which is HS, and the formulas are adjusted accordingly. I applied your formula to the Formula Test tab; however, it returns #value, which does not match the expected result. According to the sample data, the correct result should be -6,662.62. Additionally, I have tried another formula, which is also returning zero.
Could you please provide further assistance to resolve this issue? Any additional guidance or suggestions would be greatly appreciated.
Thank you for your help.
Best regards,
Regina
Jul 29 2024 10:56 AM - edited Jul 29 2024 11:42 AM
1. With your version of my formula, the #VALUE error is because the ranges start with row 1 instead of row 2. Since row 1 contains column titles, "* HX!BC1" at the end produces an error. Change all ranges to 2:63590, as they were originally.
ERRATA.... I see that you used the range 1:63590 orginally, and I copied the error. Sergei corrected the range to 2:63590, and I had not seen his posting while I was writing mine.
2. Your formulas return zero because the sample data is (again) not in the columns referenced by the formulas. For example, DV2:DV63590 is empty.
3. I still do not see 18 in any column, much less a highlighted row. So none of the conditions DV2:DV63590=18 would be TRUE, even if we assume one of the bogus columns should be column DV.
ERRATA.... I see now that E41 does indeed have the number 18 (and it is numeric, not text). I don't know how I missed that when I used ctrl+f (Find). No matter! If I assume that column E should be DV, HX!Q2:Q63590 = "0735" is FALSE for all rows because column Q is now empty. Bottom line: Provide an Excel file with all of the data in the correct locations.
4. FYI, there is no need to array-enter the SUMPRODUCT formula. OTOH, I'm not sure you did. That might simply be the way the formula in your version of Excel was interpreted in my older version of Excel (2010).
Jul 29 2024 11:03 AM - edited Jul 29 2024 11:04 AM
PS.... The logic of your SUM(SUMIFS(...), SUMIFS(...), ...) formula is certainly not the same as your original formula, as I corrected it.
Jul 29 2024 11:51 AM
Thank you once again for your prompt assistance.
I have updated the sample data and made the necessary changes to each of the formulas (please see the attached file). Despite these adjustments, I am still encountering zeros with both your formula and Sergei's formula. I am unsure why this is occurring.
Interestingly, my own formula produces the correct value. However, I am concerned that my formula might be causing a double count. If that’s the case, could you please advise on the best way to correct this issue?
Your insights and suggestions would be greatly appreciated.
Thank you for your continued support.
Best regards,
Regina
Jul 29 2024 01:05 PM - edited Jul 30 2024 12:59 PM
@ReginaAnn wrote: "my own formula produces the correct value"
Okay. I might have misunderstood the logic, especially since SUMIFS with an array constant in the condition behaves differently in my version of Excel.
-----
@ReginaAnn wrote: "I am concerned that my formula might be causing a double count"
Yes. But I am not interested in trying to make the SUM(SUMIFS(...), SUMIFS(...), ...) formula work -- if that is even possible.
However, I overlooked the fact that you had a similar problem in your original formula, as I corrected it.
The condition ((HX!P2:P41 = 94001) + (HX!P2:P41 = 91142) + (HX!P2:P41 = 94004)) works as intended (OR) because the terms are mutually-exclusive: for each row, Px can only be one value at a time.
However, that is not necessarily true for the condition ((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500") + (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500")), unless you want to rely on the assumption that the user (you?) will ensure that "5500" is in at most only one of those columns at a time.
If only for "defensive programming", it would be better to write
((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500")
+ (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500") > 0)
-----
@ReginaAnn wrote: " I am still encountering zeros with both your formula and Sergei's formula"
The primary problem is: the value in P31, P35 and P41 is not one of 94001, 91142 or 94004, as P32 is.
The second problem in my version is: the values in P31, P32, P35 and P41 are text, not numeric.
(Looks can be deceiving, and the format of the cell does not matter. Use formulas of the form =ISTEXT(P31) to confirm.)
Both problems can be fixed by replacing ((HX!P2:P41 = 94001) + (HX!P2:P41 = 91142) + (HX!P2:P41 = 94004)) with
(HX!P2:P41 = {"94001","91142","94004","90327","99327"})
With those changes, my SUMPRODUCT formula now returns the expected result in the sample data.
=SUMPRODUCT((HX!AB2:AB41 = 18) * (HX!Q2:Q41 = "0735")
* (HX!P2:P41 = {"94001","91142","94004","90327","99327"})
* ((HX!AC2:AC41 = "5500") + (HX!AD2:AD41 = "5500")
+ (HX!AE2:AE41 = "5500") + (HX!AF2:AF41 = "5500") > 0)
* HX!Y2:Y41)
-----
Alternatively, in addition to adding tests for P2:P41=90327 and P2:P41=99327 (or changing the values in P31, P35 and P41), you could employ one of Sergei's fixes, namely --HX!P2:P41 instead of simply HX!P2:P41. The double negation converts "numeric text" into bona fide numbers.
And arguably, that might be a good idea if only for "defensive programming".
To that end, I note that whereas 18 in column AB is numeric, all of the other values in column AB are text. So, in general, the first condition should be (--HX!AB2:AB41 = 18). Alternatively, change numeric 18 to text, and write (HX!AB2:AB41 = "18").
But note that "* --HX!Y2:Y41" at the end would be unnecessary. Any arithmetic operation (e.g. multiplication) is sufficient to convert "numeric text". So leave "* HX!Y2:Y41" as-is.
Jul 30 2024 08:55 AM
With the latest sample initial formula could be
=SUMPRODUCT(
(HX!AB2:AB63590 = 18)*
(HX!Q2:Q63590 = "0735")*
(
(HX!P2:P63590 = "90327") +
(HX!P2:P63590 = "91142") +
(HX!P2:P63590 = "99327")
) *
( (
(HX!AC2:AC63590 = "5500") +
(HX!AD2:AD63590 = "5500") +
(HX!AE2:AE63590 = "5500") +
(HX!AF2:AF63590 = "5500")
) > 0
) *
HX!Y2:Y63590 )
Jul 30 2024 02:45 PM
Thank you for this new code. It does work in the example; however, it does not work in my larger data set. I've updated the formula as below to correspond to the attached file. Not sure why it's not working. Any help you can provide would be greatly appreciated.
=SUMPRODUCT( (HX!DV2:DV66410 = 22)* (HX!Q2:Q66410= "0735")* ((HX!P2:P66410= "90327") + (HX!P2:P66410= "91142") + (HX!P2:P66410= "99327") ) * ( ( (HX!EN2:EN66410 = "5500") + (HX!EO2:EO66410 = "5500") + (HX!EP2:EP66410 = "5500") + (HX!EQ2:EQ66410 = "5500") ) > 0 ) * HX!BC2:BC66410)
Thank you,
Regina
Jul 31 2024 01:56 AM
SolutionThis formula
=SUMPRODUCT(
(HX!DV2:DV66410 = "-") *
(--HX!Q2:Q66410 = 735) *
(
(
(--HX!P2:P66410 = 90327) +
(--HX!P2:P66410 = 91142) +
(--HX!P2:P66410 = 99327)
> 0
)
) *
(
(
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500)
> 0
)
) *
HX!BC2:BC66410 )
returns some result, but here are not exactly the same column as in sample. In general
- you need to define which columns to use. If they could be in different positions it's better select columns based on headers in first row
- Do you have numbers, or texts which looks like numbers, or any other text like "-", or combination of texts and numbers in columns
Depends on above formula could be modified to make it more universal.
Jul 31 2024 09:50 AM
@SergeiBaklanthanks for being tenacious. I was able to get this to work with your help.
Have a wonderful rest of the day!
Regina
Jul 31 2024 09:54 AM
@ReginaAnn , you are welcome, glad it helped
Jul 31 2024 01:56 AM
SolutionThis formula
=SUMPRODUCT(
(HX!DV2:DV66410 = "-") *
(--HX!Q2:Q66410 = 735) *
(
(
(--HX!P2:P66410 = 90327) +
(--HX!P2:P66410 = 91142) +
(--HX!P2:P66410 = 99327)
> 0
)
) *
(
(
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500) +
(--HX!DL2:DL66410 = 5500)
> 0
)
) *
HX!BC2:BC66410 )
returns some result, but here are not exactly the same column as in sample. In general
- you need to define which columns to use. If they could be in different positions it's better select columns based on headers in first row
- Do you have numbers, or texts which looks like numbers, or any other text like "-", or combination of texts and numbers in columns
Depends on above formula could be modified to make it more universal.