Sumproduct using text and values

[BuzzG]

Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria. Any ideas or threads to follow?
BuzzG

 

[Aladin Akyurek]

I ****-umed* it was a text-formatted numeric problem in the lqst range. Good evening, Aladin -- no more *, correct?

Only when...

=SUMPRODUCT((A1:A10="X")*(B1:B10="y")*(C1:F10))

 

[BuzzG]

Thank you Aladin for your insight. Also the question- What is worksheet.function? A Web Forum?
BuzzG

 

[Aladin Akyurek]

...Also the question- What is worksheet.function? A Web Forum?
BuzzG

An MS newsgroup. In extenso: microsoft.public.excel.worksheet.functions

 

[just_jon]

Thanks; might need to check w/ you once more on */, in SUMPRODUCT, but will look at the MS group 1st.

 

[uneakbreed]

i know this is a super old post but wow. this ****** has SAVED me. i'm constantly needing to query tracking sheets created by other people and was always stuck as they would put both text and number values and always had to create far too elaborate subsheets to parse just the numerical data out to then run a sumproduct formula against. but, not anymore!

THIS is the MOST useful post i've ever found. Sorry to ressurrect ancient posts, just had to give my kudos.

 

[deckerp]

I respond to this thread as I have a similar issue and dont get it to work.

my formula:
=SUMPRODUCT(--(RES_Project.Category=B57);--(RES_Accure.At="End");(RES_End.Date=YEAR(E$55));(RES_End.Date=MONTH($E55));(RES_Attributed.Costs))


B57 = Text

RES_Project.Category (Text)
=OFFSET(Resources!$E$9;;;MATCH(REPT("Z";255);Resources!$E$9:$E$506))

RES_Accure.At (Text)
=OFFSET(Resources!$P$9;;;MATCH(REPT("Z";255);Resources!$P$9:$P$506))

RES_End.Date (Date)
=OFFSET(Resources!$G$9;;;;MATCH(9,99999999999999E+307;Resources!$G:$G)-ROW(Resources!$G$9)+1)

RES_Attributed.Costs (amount EUR)
=OFFSET(Resources!$O$9;;;;MATCH(9,99999999999999E+307;Resources!$O:$O)-ROW(Resources!$O$9)+1)

Thanks for your help.

 

[Aladin Akyurek]

I respond to this thread as I have a similar issue and dont get it to work.

my formula:
=SUMPRODUCT(--(RES_Project.Category=B57);--(RES_Accure.At="End");(RES_End.Date=YEAR(E$55));(RES_End.Date=MONTH($E55));(RES_Attributed.Costs))


B57 = Text

RES_Project.Category (Text)
=OFFSET(Resources!$E$9;;;MATCH(REPT("Z";255);Resources!$E$9:$E$506))

RES_Accure.At (Text)
=OFFSET(Resources!$P$9;;;MATCH(REPT("Z";255);Resources!$P$9:$P$506))

RES_End.Date (Date)
=OFFSET(Resources!$G$9;;;;MATCH(9,99999999999999E+307;Resources!$G:$G)-ROW(Resources!$G$9)+1)

RES_Attributed.Costs (amount EUR)
=OFFSET(Resources!$O$9;;;;MATCH(9,99999999999999E+307;Resources!$O:$O)-ROW(Resources!$O$9)+1)

Thanks for your help.

What do you have in E55 exactly?

 

[deckerp]

What do you have in E55 exactly?

Content of E55
=IF($B9="";"";DATE(YEAR(MIN(ACT_Actual.FinishDate));MONTH(MIN(ACT_Actual.FinishDate))+Par!S9;DAY(MIN(ACT_Actual.FinishDate))))

Format of E55
MMM JJ (eg. Feb 10)

Content of PAR!S9 = (numbers to adjust the date range, currently 0)

 

[Aladin Akyurek]

Content of E55
=IF($B9="";"";DATE(YEAR(MIN(ACT_Actual.FinishDate));MONTH(MIN(ACT_Actual.FinishDate))+Par!S9;DAY(MIN(ACT_Actual.FinishDate))))

Format of E55
MMM JJ (eg. Feb 10)

Content of PAR!S9 = (numbers to adjust the date range, currently 0)

What do yet get if you carry out the following test:

=ISNUMBER(E55)

 

[deckerp]

What do yet get if you carry out the following test:

=ISNUMBER(E55)

result: TRUE