Is there a way to feed an array of 'IF' function results into a 'Sumproduct' function? For example something like this =sumproduct(--(If(CODE(a1:b12)=32,0,a1:b12))). I want sumproduct to add up all the 0's and the values in the other cells. I have modified my question and added an example.
Last edited by bird333; 10-22-2020 at 12:01 PM.
Don't say, "something like ...". How do we know what that means?
See the yellow banner. Post a sample workbook.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
=SUMPRODUCT(--(CODE(a1:b12)=32))
and
=SUMPRODUCT(--(NOT(CODE(a1:b12)=32)))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Example has been added.
Thanks. See proposed solution in post #3.
Based on your example, why not
=SUM(E3:G4)
?
Thanks for response. These return the number of cells that have a space or not. This doesn't return the total I'm looking for.Originally Posted by ChemistB
I have attached another example. I need a modification to the formula in cell J2 that will treat any blank cells or cells that have spaces in the area as a '0' value. The way it is now it causes the formula to fail. An IF function could in effect replace the blanks and spaces with '0' but how to get those values passed to the Sumproduct function is the issue. The formula should return $5550. BTW there is a space in cell G10.
Before Excel 2019/Excel in latest Office/MSFT 365, IF won't return array results unless fed an array constant as an argument or if the formula is entered as an array formula ([Ctrl]+[Shift]+[Enter]).
Given your example formula and the data in your sample workbook, you could just use =SUM(E3:G4).
More generally, you could also use the ARRAY FORMULA =SUM(IF(CODE(A1:B12)<>32,A1:B12)).
If I'm understanding you it sounds like this is not possible. This was just a sample, there are more sections and I don't want to sum items on the 'paid' lines so I need to filter only the 'billed' lines and I only want items equal or less than the month number (3 in the example). That's why I don't just use 'SUM'.
This formula seems to work but the problem is the 'Code' function. When it encounters a blank cell it returns #value. Is there a way to make 'code' return '1'?
=SUMPRODUCT((RIGHT($A$6:$A$12,6)="Billed")*($E$4:$P$4<=$E$1)*($E$6:$P$12)*((ISBLANK($E$6:$P$12)=0)+(CODE($E$6:$P$12)<>32)))
Why is there a space in g10?
This array formula does work...
=SUMPRODUCT(IFERROR((ISNUMBER(SEARCH("Billed",$A$6:$A$12)))*($E$4:$P$4<=$E$1)*$E$6:$P$12,0))
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thanks! There is a space in G10 because occasionally there are spaces in the range and I'm trying to deal with that. Is there a non-array option to solve this?
Not one that always works, in my experience... But maybe that's down to me being thick....
Why not SUMIFS?
=SUMIFS(values_range,status_range,"billed",date_range,"<"&DATE(year_number,month_number+1,1))
whould seem to be what you want.
If it has to be an array formula, no benefit using SUMPRODUCT rather than just SUM.
In Excel 2019/Excel in Office/MSFT 365, spilled formulas, which are very similar to array formulas, must be entered in the topmost-leftmost cell, but even those latest Excel versions accept traditional array formulas. If you select B2:D7 with cell C6 the active cell, type the formula =ROW()/COLUMN(), hold down [Ctrl] and [Shift] keys and press [Enter], you will enter the array formula into all cells in B2:D7.Array Formulae . . . MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER . . .
Last edited by hrlngrv; 10-20-2020 at 04:27 PM.
Looks like the following non-array/standard formula would work.
Formula:
Please Login or Register to view this content.
See this, cell H2.
Last edited by hrlngrv; 10-20-2020 at 08:58 PM. Reason: added link to example workbook
Thanks for the response. This looks like it could work but it doesn't (just returns 0). However, when I try to step through 'evaluate formula, Excel closes when I get to the 'IFERROR' function. Anybody know why? Also why are the '--' needed in the IFERROR function?
Do you mean Excel crashes? If so, you have a serious problem which needs fixing ASAP. If this is on a work PC, you need to report this to IT. If on a home PC, go to Control Panel's Programs and Features section, right click on the MS Office entry and if there's a Fix Office entry, click on it.
The -- forces a numeric conversion. For cells in E6:P12 which contain numbers, this does nothing as it changes the arithmetic sign twice, which returns the values to their original sign. For nonnumeric text, it throws errors, which IFERROR replaces with 0. This would convert numeric text and boolean values to numbers, so it's not a universal solution. If you could have numeric text or boolean TRUE in E6:P12 which you don't want to convert to numbers so include in the sum, array formulas would be the only robust option.. . . why are the '--' needed in the IFERROR function?
=SUM(IF(ISNUMBER($E$6:$P$12),$E$6:$P$12*ISNUMBER(SEARCH(" Billed",$A$6:$A$12))*($E$4:$P$4<=$E$1)))
Yeah, it is crashing. It shows 0 as the solution. I ran the office repair and it is still crashing on 'evaluate formula' at the IFERROR function. I tried it on another Excel machine and same thing. The versions were Excel 2013 and 2016. Occasionally on the 2016 machine the evaluate formula will work, when it does it seems to change the e6:p12 area to #value and then the whole IFERROR function evaluates to 0 which is then multiplied times the rest of the results in SUMPRODUCT which of course makes the whole answer 0.
Last edited by bird333; 10-20-2020 at 10:49 PM.
Excel 2016, try formula in #17, for me, it works ONLY with Ctrl-shift-enter combination.
Quang PT
All I can say is look at the example file for which I provided a link.
If Excel 2013 or 2016 are crashing on IFERROR, then you may be forced to use array formulas.
ADDED:
I used a VM which still has Excel 2010, and I see the problem. IFERROR(--range,0) requires array formula entry. This requires more cleverness to avoid array formula entry. Try
Formula:Please Login or Register to view this content.
Last edited by hrlngrv; 10-21-2020 at 12:28 AM. Reason: addendum
hrlngrv... your
=SUMPRODUCT(ISNUMBER(SEARCH(" Billed",$A$6:$A$12))*($E$4:$P$4<=$E$1)*IFERROR(--$E$6:$P$12,0))
at Post 17,is essentially the same as mine (Post 12) and must be set as an array in 2016... for me anyway. You suggested SUMIFS... how?
However, your last suggestion in Post 22
=SUMPRODUCT(ISNUMBER(SEARCH(" Billed",$A$6:$A$12))*($E$4:$P$4<=$E$1)*TEXT($E$6:$P$12,"General;-General;0;\0"))
is really nice!! Have some rep!!
Finally, are you who I think you might be??
Last edited by Glenn Kennedy; 10-21-2020 at 01:32 AM.
Post numbers depend on the threading model used. Using the one I prefer, my post was #20.
deleteme-ID.png
More general to refer to posts by their date-time stamp.
The timestamp in your pic, in the post above, shows as 08:25 PM. On my PC it shows as Today 04:25 AM, so date-time stamps aren't consistent either!!
Fair point. Time zones.
There's no universal way of identifying previous posts other than quoting them in their entirety.
Actually, not true. Hover over the Report Post link, and the url displays like https://www.excelforum.com/report.php?p=0000000, so p=0000000 would seem to be a universal ID for specific posts. Shame it's relatively unusable.
Thanks this works. Could you please explain how particularly the 'text' portion? Also is it necessary to have the space in from of 'Billed'?
Excel automatically converts text which looks like numbers into numbers when used in arithmetic operations, in this case multiplication. TEXT(number,"...") converts number to a text representation given in the 2nd (format) argument. Number formats can have 4 parts, separated by semicolons: for positive numbers, for negative numbers, for zero, and for text/booleans. General is the most flexible representation, so used for positive and negative (including leading - for negative). 0 for zero is OK. \0 for text/boolean means display any text or boolean value as "0" (the leading \ makes it a literal zero rather than a placeholder for a decimal numeral). So TEXT with that number format 2nd argument converts number values to their text representation and text/boolean values to "0", and using the results in multiplication just converts numbers back to numbers and treats original text/boolean values as zero.
Probably not, but I've had too many surprises in the past by assuming I didn't need explicit substring separators. Habit on my part to include such spaces.is it necessary to have the space in from of 'Billed'?
Thanks for the rep. But the real credit is due to hrlngrv... who provided a workaround that I had never seen before.
We merely stand on the shoulders of giants....
Thanks, but it's likely I saw that somewhere else. There's very little original in Excel formulas. Even things involving LET, FILTER, SORT and UNIQUE can be traced back to stats packages or APL.
That said, this is likely an instance in which the array formula approaches using SUM rather than SUMPRODUCT were best as in most robust and efficient.
How about
=SUMPRODUCT((RIGHT(A6:A12,6)="Billed")*(E4:P4<=E1),E6:P12)
Thanks it works too! What exactly does the comma do that's before the final range?
Let me rephrase. Why does the above formula work, but the exact same formula with a '*' instead of a ',' gives #Value? Excel help says the default for sumproduct is multiplication so I would think these two formulas would be equivalent.
=SUMPRODUCT((RIGHT(A6:A12,6)="Billed")*(E4:P4<=E1),E6:P12) [works]
=SUMPRODUCT((RIGHT(A6:A12,6)="Billed")*(E4:P4<=E1)*E6:P12) [doesn't work]
When you use the multiplication (*), it forces Excel to multiply the content of each cell in E6:P12 by either 1 or 0. If there is text in any of those cells, you'll get an error #Value. When you use the comma, it ignores cells with text in them. Just the way Microsoft set it up.
ChemistB... isn't it worse than that. When I first looked at this thread, I used this:
=SUMPRODUCT((ISNUMBER(SEARCH("Billed",$A$6:$A$12)))*(E4:P4<=E1),E6:P12) which returns #VALUE... I then went off at a tangent, winding up with the array. Using a comma instead of the red asterisk would have made it work perfectly.
Thanks for the explanation.
Actually that formula works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks