SUMIF formula needs criteria, or help me with another formula please - with example.

Hi,

I'm attaching a little example of an excel sheet. The actual one is confidential and huge (over 40 columns 650 rows, and 20+ sheets) it also has many macros and apart from column A and B all columns are full with calculations. It has been developed over years through many people.. some knew what they do some didn't, and so far no one made an effort to clear it up.. to add new rows we have a macro, but we can not add new columns, as some of the macros and calculations break. I also think it is not necessary for this calculation that I need to add in, but it could be done with a pivot table in my example, in the excel sheet I have it is not possible.

So: First column is strawberries or blueberries second column is random weight values. The third column decides if they are counted or not (0 or 1) the fourth column is =B*C and D2 is a SUM of D column.

In the C column for the strawberries there is a number, either 0 or 1 but for the blueberries it refers to other cells which are also 0s or 1s but this allows for the blueberries to be counted in cell D2 if we want them to be counted or to not be counted when we don't want them counted. (I need to keep it like that!)

Now the complication is: for the blueberries in C column we refer to 2 other cells, one of them is "Blueberries qtty" the other one is "Additional blueberries". I would need to SUM the Blueberries but not the ones that are additional.
NO MATTER IF THE CELL VALUE IN C IS 0 OR 1 IT ALWAYS NEEDS TO BE THE SAME NUMBER (-4 in the example). There are Blueberries with Negative value in column B, so <0 doesn't work because the negative ones will be counted but they are additional so they shouldn't be. (Row 7 in the example)

Formula would be something like: =SUMIF(A5:A13,"Blueberry",B5:B13) but this doesn't take into consideration that I don't want the "Additional blueberries" counted.

So in my head it should look something like: =SUMIFS(B5:B13,A5:A13,"Blueberry",C5:C13, ???PLEASE HELP???) so how do I express in excel language: C column, whichever row, cell contains =$L$9 cause "=$L$9" doesn't seem to work.. and so far nothing else I tried.

If there is another formula that would do and fit the my excel sheet as I explained above, then I'm open for it just please explain what your formula does.. I'm not really good in excel and I would like to learn.

=SUMIFS($B$5:$B$13,$A$5:$A$13,"Blueberry",$C$5:$C$13,1)

or

=SUMIF($A$5:$A$13,"Blueberry",$D$5:$D$13)

like this one?
=SUMIFS($B$5:$B$13,$B$5:$B$13,"<"&$L$9,$C$5:$C$13,$L$6,$A$5:$A$13,"Blueberry")

too late refresh

something different
=SUMPRODUCT(($A$5:$A$13="Blueberry")*($B$5:$B$13<$L$9)*($C$5:$C$13=$L$6)*$B$5:$B$13)

Did you try the two formulae? they BOTH give -4, which was (I believe...) your expected answer.

@Sandy666
Thank you but the first SUMIF formula is the one that I have in the example currently. It is not good because I have both positive and negative numbers among the additional Blueberries, so it will add the negative Additional blueberries..

The SUMPRODUCT doesn't work either if the value in C changes from 0 to 1.

=SUMIFS($B$5:$B$13,$B$5:$B$13,"<"&$L$9,$C$5:$C$13,$L$6,$A$5:$A$13,"Blueberry")
SUMPRODUCT(($A$5:$A$13="Blueberry")*($B$5:$B$13<$L$9)*($C$5:$C$13=$L$6)*$B$5:$B$13)

result for both : -4 (with conditions in the workbook: qtty = 1, additional = 0) if you change conditions result will change also

=SUMPRODUCT({0;0;0;0;0;1;1;0;0}*{1;2;-3;-3;4;-1.5;-2.5;5;10})
result of conditions {0;0;0;0;0;1;1;0;0}
range of your data {1;2;-3;-3;4;-1.5;-2.5;5;10}
0 eliminate unnecessary values, so result will be: SUM of these two -1.5;-2.5

like this one?
=SUMPRODUCT(($A$5:$A$13="Blueberry")*($B$5:$B$13<0)*($C$5:$C$13<>$L$9)*$B$5:$B$13)
or
=SUMIFS($B$5:$B$13,$B$5:$B$13,"<0",$A$5:$A$13,"Blueberry",$C$5:$C$13,"<>"&$L$9)

L9 = 0 don't sum/count
L9 <> 0 sum/count

what should be in L9 to NOT sum/count ?
and should be compatibile with C column

NO, FALSE, whatever ?

ok, I'll try again....

Conditions to sum:

A column = Blueberry
B column = less then 0
these two are for sure, right?

any other condition?
from C column? if needed
from L9? if needed

simple english please

but C13 has a value from L9 so it can be 0 or 1 or anything else
=SUMPRODUCT(($A$5:$A$13="Blueberry")*($C$13<>0)*$B$5:$B$13)
I did for C13<>0 then sum

$L$9 is an address of the cell NOT value

what kind of values will be in L9? could you list it?

btw. the last row (13) should be in the range of SUM?

another way: range of SUM is: $B$5:$B$13 or $B$5:$B$12

Finally, I understand what you want.... I think. Use this array formula:

=SUMPRODUCT(($A$5:$A$13="Blueberry")*(IFERROR(FORMULATEXT($C$5:$C$13),"")<>"=$l$9")*$B$5:B$13)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

=SUMPRODUCT(($A$5:$A$13="Blueberry")*(IFERROR(FORMULATEXT($C$5:$C$13),"")<>"=$l$9")*$B$5:B$13)

Red: returns True or False, depending on whether A5:A13 = "Blueberry"
Orange: returns the text of any formulae in the cell range, or #N/A if no formula
Cyan: converts any #N/A to blanks, and
Blue: returns TRUE for anything that isn't"=$L$9" and multiplies it by the first set of true/false: T.T=T, T.F=F, F.F=F, etc


This returns a series of 0s and 1s in an array {0,0,0,0,0,1,1,0,0} to be exact.

SUMPRODUCT multiples those numbers by the content of B5:B13 (purple) and adds up the answers....

Done.

I've been going round in ever decreasing circles and can no longer see the wood for the trees.

This works, but it's ugly:

=SUMPRODUCT((ISNUMBER(SEARCH("Blueberry",$A$5:$A$15)))*IF((NOT(IFERROR(FORMULATEXT($C$5:$C$15),"")="=$L$10")+NOT(IFERROR(FORMULATEXT($C$5:$C$15),"")="=$p$12")+NOT(IFERROR(FORMULATEXT($C$5:$C$15),"")="=$P$13"))=3,TRUE,FALSE)*$B$5:B$15)

I need to sit in a dark room for a while...