Sumproduct Help - Referencing Text String

Good Day,

I am having issues with a sumproduct formula referencing a text string parced out from a pivot table.

SUMPRODUCT(--('Invoice Detail'!$V$5:$V$913=LEFT(B75,FIND("Total",B75)-1))*--('Invoice Detail'!$H$5:$H$913>60)*--('Invoice Detail'!$H$5:$H$913<=90),('Invoice Detail'!$S$5:$S$913))-(SUM($M$6:$M74)-SUM($M$6:$M74))

The highlighted is the part of the code that I know is the issue. In this case the value of B75 is "GLOBAL SHARED SERVICES Total". My other spreadsheet references only "GLOBAL SHARED SERVICES". I've tried parcing out the phrase in another column and referencing that cell, but that does not work either. Can someone please advise?

Originally Posted by mrsogmax76

=SUMPRODUCT(--('Invoice Detail'!$V$5:$V$913=LEFT(B75,FIND("Total",B75)-1))*--('Invoice Detail'!$H$5:$H$913>60)*--('Invoice Detail'!$H$5:$H$913<=90),('Invoice Detail'!$S$5:$S$913))

On an aside - if using * there is no need for double unary also else you're needlessly repeating coercion - to explain by means of (overly) simplistic example:

=--"1" -> 1
="1"*1 -> 1

thus

=--"1"*1

involves one lot of unnecessary coercion

On that basis, either:

=SUMPRODUCT(('Invoice Detail'!$V$5:$V$913=LEFT(B75,FIND(" Total",B75&" Total")-1))*('Invoice Detail'!$H$5:$H$913>60)*('Invoice Detail'!$H$5:$H$913<=90),'Invoice Detail'!$S$5:$S$913)

or

=SUMPRODUCT(--('Invoice Detail'!$V$5:$V$913=LEFT(B75,FIND(" Total",B75&" Total")-1)),--('Invoice Detail'!$H$5:$H$913>60),--('Invoice Detail'!$H$5:$H$913<=90),'Invoice Detail'!$S$5:$S$913)

(both of the above were adjusted re: your initial issue also - when applying a FIND in this manner it's often a good idea to append the string with the search term so as to avoid possibility of error being generated (eg were B75 just equalled GLOBAL SHARED SERVICES - no Total)


If you wanted to you could also replace:

*('Invoice Detail'!$H$5:$H$913>60)*('Invoice Detail'!$H$5:$H$913<=90)

with

*(CEILING('Invoice Detail'!$H$5:$H$913,30)=90)

CEILING is only really useful where there is an even pattern to the numbers of interest.

In this case (>60 to <=90) CEILING was viable - rounding up all values to the lowest multiple of 30 >= current value.
If follows that any value > 60 and <=90 would generate 90 ... all other values would generate values other than 90.

(FLOOR does the same but in the opposite direction)