Can SUMIFS result be text?

[maku]

I successfully use SUMIFS formula to with INDEX(MATCH) to return a result from a specific worksheet that matches the multiple criteria in the formula.


I just tried to pull in a result that is a text value and not numerical. It returns a "0". I have formatted the cells using different criteria (GENERAL, TEXT) and still the same result "0".


DOes SUMIFS have a limitation of not being able to return a result that is text? If so, is there an alternative?


Thanks, -Maku

 

[Debraj]

Hi Maku,


I just give a try to the below post..

http://chandoo.org/forums/topic/trying-to-look-up-text-match-in-a-cell-and-display-count-of-other-range#post-57564


Hope you can manage to adapt the same in your formula..

Otherwise, Please comeback with some sample data/sheet..


Regards,

Deb

 

[maku]

Thanks Deb...I'll give it a shot and let everyone know.

 

[maku]

Deb...that's actually a little different function I think than what I'm trying to achieve. Your example searches for a text string.


I'm looking to return a cell value based on multiple criteria. It works if the cell value is a number, but apparently not if TEXT.


My current formula is:

[pre]

=SUMIFS(INDEX(INDIRECT("'"&$G$9&"'!A:ZZ"),,MATCH("COLUMN HEADING I WANT",INDIRECT("'"&$G$9&"'!1:1"),0)),INDEX(INDIRECT("'"&$G$9&"'!A:ZZ"),,
MATCH("MONTH",INDIRECT("'"&$G$9&"'!1:1"),0)),parameters!A$68,
INDEX(INDIRECT("'"&$G$9&"'!A:ZZ"),,MATCH("YEAR",INDIRECT("'"&$G$9&"'!1:1"),0)),parameters!A$66)

[/pre]
So, it matches the column heading I want to retrieve based on the criteria of Month and Year. The indirect $G$9 references are to a cell that links to the worksheet tab name.


I am guessing the SUMIFS has a limitation to not be able to return a TEXT value. Is this true?

 

[Debraj]

Hi Maku,


Can you please upload sample workbook,


I Know..

* it a huge workbook.

* with more than at-least three + sheet, (parameter, and variable text in G9)

* where sheet name parameter's A66 & A68 are Year & Month,

* and In the Sheet name in G9 has 702 Rows (A:ZZ) which are may be consist Month & Year word and with multiple time.. as you are matching only Month / Year word..


but still its really hard for me to check > Verify > re-check > Re-verify, and then submit the proposed answer..


as I think, it may be solved by Address,Indirect & Offset only..


Oh yes!.. SUMIFS only can SUM, Numeric Value..


Regards,

Deb

 

[maku]

A formula that works is:


INDEX(C1:C4,MATCH(1,(A1:A4="x")*(B1:B4="y"),0))


Where: column C is the data wanting to be retrieved. Column A and B are criteria being matched. *Note..must be entered as an array (CTRL-SHIFT-ENTER)

 

[Montrey]

So did you solve your problem or????

 

[maku]

Yep..problem solved the above array formula works and allows a TEXT result.