SUMIF: formula as criteria not working for entire column

2
votes

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?

2
excelexcel-formulasumifs
SUMIF only sums up rows where the criteria range (B:B) matches the value you specify. Your formula makes no sense in that context, since you are creating an array of True/False values as the criteria, and there are no True/False values in column B. – Rory
Not sure what your outcome should be but maybe try: =SOMPRODUCT(ALS.FOUT((C:C/(2,8*0,0735)/B:B)=1;0)*B:B) or in English: =SUMPRODUCT(IFERROR((C:C/(2.8*0.0735)/B:B)=1,0)*B:B) – JvdV
@BRTN, did my answer work? I'm happy to work on it if it didn't meet your needs. Just let me know. – I like Excel very much
@JvdV I tried it, but it didn't do the trick. Thanks! – BRTN

2 Answers

1
votes

Yes, it is possible. Try this one:

{=SUM(--(B:B=C:C/(2.8*0.0735))*IF(ISERROR(1/B:B),0,1))}

Remember to enter it as an array function with CNTRL + SHIFT + ENTER.

The first half of the formula is just a logical test, after the asterisk it tests if 1/B results in an error (thereby omitting text, zeroes, and blanks) and returns a zero if there is an error.

These are then summed and the result displayed.

1
votes

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.