Sum negative numbers from specific cells

[windoz]

Hi,

I've been looking for a solution to a problem which I have. I want to count all the negative numbers from a range of specific columns. However searching google and the forums here doesn't necessarily provide me with a solution.


I've used Countif(range,"<0"), and also Sumifs, I still have the issue.
The problem with this formula is that if I select specific columns like C1, E1, G1 it states "you've entered too many arguments for this function".

The range I have is at least 25 different column cells to look at and just need to count (sum) for all the negative numbers only.

Can this be done ?

Using Office 2019 on a Windows 10 Desktop.

Thank you in advance.

[Pepe Le Mokko]

Administrative note

Welcome to the forum

in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

Please take a moment to read it and attach a sheet accordingly.

Thanks you for helping us help you

[windoz]

Sorry, please see the attached.

For example I need to count the negative numbers for person 2 for August then October then December. I don't need the other months.

Much appreciated.

[bird333]

Sorry, please see the attached.

For example I need to count the negative numbers for person 2 for August then October then December. I don't need the other months.

Much appreciated.

I wasn't sure if you wanted a sum of the negative numbers or the number of negative occurrences. Try these in cell N3.

=SUMPRODUCT((B3:M3)*(B3:M3<0)*(($B$1:$M$1="Aug")+($B$1:$M$1="Oct")+($B$1:$M$1="Dec"))) (Gives sum of negative amounts)
=SUMPRODUCT(--(((B3:M3)*(($B$1:$M$1="Aug")+($B$1:$M$1="Oct")+($B$1:$M$1="Dec")))<0)) (Gives the number of negative occurrences)

[Pepe Le Mokko]

@bird
Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

and pull down

[windoz]

Pepe Le Mokko, Thank you for your time to respond and I really appreciate the solution you have provided.

[Pepe Le Mokko]

You're welcome

[hrlngrv]

. . .

=SUMPRODUCT((B3:M3)*(B3:M3<0)*(($B$1:$M$1="Aug")+($B$1:$M$1="Oct")+($B$1:$M$1="Dec"))) [sum]

=SUMPRODUCT(--(((B3:M3)*(($B$1:$M$1="Aug")+($B$1:$M$1="Oct")+($B$1:$M$1="Dec")))<0)) [count]

For what it's worth, you could condense these to

=SUMPRODUCT((B3:M3<0)*($B$1:$M$1={"Aug";"Oct";"Dec"}),B3:M3) [sum]

=SUMPRODUCT((B3:M3<0)*($B$1:$M$1={"Aug";"Oct";"Dec"})) [count]

[bird333]

For what it's worth, you could condense these to

=SUMPRODUCT((B3:M3<0)*($B$1:$M$1={"Aug";"Oct";"Dec"}),B3:M3) [sum]

=SUMPRODUCT((B3:M3<0)*($B$1:$M$1={"Aug";"Oct";"Dec"})) [count]

Thanks! It's always good to learn more about sumproduct.

[hrlngrv]

The thing to note is B1:M1 is a 1-row by 12-column range, {"Aug";...} a 3-row by 1-column array, and B1:M1={"Aug";...} is then a 3-row by 12-column derived array.