Search code examples
excelexcel-formulasumifs

SUMIF: formula as criteria not working for entire column

My worksheet contains orders from clients. The orders are all wooden panels.

IMG: Section of the sheet with attempted formula

Every order is assigned a number which is led by the letter Q.

  • Column B contains the number of parts in the order.

  • Column C contains the total m² in the order.

Orders that contain one or more parts that are 2.8 x 0.0735 m will get a row of their own.

I'm trying to count the number of times that this part occurs in a list of more than a thousand rows.

So if I divide the total m² by the m² of the part I'm looking for and divide this by the amount of parts in the order, I should get exactly 1 as a result. If I take the sum of all the number of parts that result in a 1, I get my total.

Now I'd like to put this in one formula for the entire worksheet, but SUMIF doesn't work the way I'm trying. (It's in Dutch)

=SOM.ALS(B:B;(C:C/(2,8*0,0735)/B:B)=1)

I can't seem to use this formula as a criterium in the SUMIF.

For now I use a helping column that gives the right amount per row. Then take the total SUM of these.

Is it possible to put this in a single formula?

Solution

  • In Dutch and English:

    {=SOMPRODUCT(--(B:B=(ALS(ISTEKST(C:C);1;C:C))/(2,8*0,0735));B:B)}


{=SUMPRODUCT(--(B:B=(IF(ISTEXT(C:C),1,C:C))/(2.8*0.0735)),B:B)}

    is working perfectly. (Enter with Ctrl-Shift-Enter)

    The first bit is the logical test, which will check if B:B = C:C / (2.8*0.0735)

    It got stuck on #VALUE! because there is text in C:C. The IF(ISTEXT)) eliminates text by converting them to numeric values, in this case 1, but it can be any numeric value.

    The logical test will return TRUE(1) or FALSE(0) because of the double dash or unary operator and this will be multiplied by their respective B:B value.

    Because the row with text has no value in B:B, it will result as zero.