SUMIF for a range of one cell and a sum range of 242

SergeiBaklan . Sergei, nearly completed this 200MB workbook. Just one more piece of the puzzle if I may? I am trying to do the following =IF($R3637:$R3646=GLZ$3,SUMIFS($S3637:$JF3646,$S$1:$JF$1,"Sold"),0). Basically if any cell in column R in the range of 3637:3646 = GLZ3, then sum all values IN THE SAME ROW ONLY where the GLZ3 value occurs in the row range 3637:3646 - in the array of $S3637:$JF3646 - so long as the value in the cell at the top of the corresponding column of the array in Row 1 = "Sold". 

The formula as I have it now, returns "VALUE". 

I can construct the above in a laborious way by entering for each row the following =IF($R3637=GLZ$3,SUMIFS($S3637:$JF3637,$S$1:$JF$1,"Sold"),0) and this works, but given there are 20 sections to each row and 10 rows (i.e. requiring 200 variations of the above formula, that would mean exceeding the formula characters allowed in a single Excel cell. I am seeking a tidy solution to this and would be hugely grateful for yr help once again. V best

SergeiBaklan - Serge - many thanks for the advice. Now works a treat. Hadn't appreciated the functionality of an asterix on its own! 

SergeiBaklan - many many thanks Sergei. I'm in Asian time zone, so will try that out in the morning and report back. V best.

Matt_81120 

Hi Matt,

Asterisk means any text. To use it as a character you shall use it with tilde "~*"

=IF($Q8=GHS$3,SUMIFS($R8:$IY8,$R$1:$IY$1,"~*"),0)

Above if the cell value is asterisk. If it, for example, within the text, when like "*~**"

Please check Sum if cells contain an asterisk for more details. 

SergeiBaklan - Sergei not sure whether you are still out there and hope all well? Picking up from where I left off on this project when we were last talking in April. You kindly helped me with the following formula =IF($Q8=GHS$3,SUMIFS($R8:$IY8,$R$1:$IY$1,"*"),0). Now starting to use this piece of the worksheet, I notice that this is not quite doing what I want it to do. What I want it to do is to SUM the values only in cells where the first row of the column has the "*" in it. The above formula is not achieving this. If there is a "*" in any of the range ($R$1:$IY$1), then all values in all cells in range $R8:$IY8 are being SUM'd, rather than just the cells where the columns are "headed" with "*". Is there a small tweak required to right this formula? Many thanks Sergei!

Matt_7 

Matt - yes, exactly. Glad to help.

SergeiBaklan - OK I fully get it now. As you say sum range and criteria range need to be congruent if using SUMIF or SUMIFS. If not congruent then need to use IF at the outset and then SUMIF or SUMIFS for the congruent part of the equation. A huge thanks again my friend. I really am very grateful for yr help!! Best

Matt_7 

In your formula sum range and first criteria range are of different size (the latest just one cell), thus formula returns an error. If remove it as

=SUMIFS(FXL10:GGO10,FXL1:GGO1,"E.D.F Sold")

formula returns some result. However, I guess you'd like receive this result if only FXG10 is equal to 1, when

= IF(FXG10=1,SUMIFS(FXL10:GGO10,FXL1:GGO1,"E.D.F Sold"),0)

SergeiBaklan - back again my friend for one more piece of advice if I may. Trying to SUM (in cell GHD10) a range of cells in the same row FXL10:GGO10 if cell FXG10 = 1 and if the corresponding criteria range of cells in Row 1 (FXL1:GGO1) = "E.D.F Sold". Seems simple enough to me, but the following formula returns the dreaded #VALUE! and not the value it should. I've gone through the syntax on the formula multiple times and cannot seem to find an issue. Is there something simple I am missing? I am entering the following formula =SUMIFS(FXL10:GGO10,FXG10,"1",FXL1:GGO1,"E.D.F Sold"). All help hugely welcome! Best

Matt_7 , thank you and take care, be safe.

SergeiBaklan - You are a legend my friend! 100% works. Thank you very much for your patience and your focus on this. Very much appreciated. I wish you and your family safe passage through this pandemic. Very best.