CODEWITHSUNDEEP
excelexcel-formula
MA84
MA84

Reputation: 107

How can i calculate the SUM of various cells within a COUNTIFS?

I have a database in excel where columns hold lots of different pieces of data. On a different sheet I would like to be able to perform a SUM within a COUNTIFS statement.

The logic in my head is this, but obviously it does not work:

=COUNTIFS($B:$B,$F4,(SUM($C:$C+$D:$D)),">=2")

So here I am saying if in column B of the database the value is equal to what I have in cell F4 on my sheet, then I would like, for each row where the value is what I have in F4, to add up the values between cells C and D, and if that value is greater than 2 then count it, if not then do not count it.

Here is a picture of expected output:

"expected output"

Upvotes: 0

Views: 1475

Answers (2)

JvdV
JvdV

Reputation: 75860

Here is an option based on your sample data, however by the looks of it I get different results than yours:

enter image description here

Formula in G4:

=SUMPRODUCT(($B$4:$B$12=$F4)*($C$4:$C$12+$D$4:$D$12>=G$3))

Note: I changed cell's G3:H3 value to be able to reference them.

Drag down and right. For a dynamic reference style you might try:

=SUMPRODUCT((F2:INDEX(F:F,COUNTA(F:F))=C3)*(H2:INDEX(H:H,COUNTA(H:H))+I2:INDEX(I:I,COUNTA(I:I))>=2))

Upvotes: 1

LafaMan
LafaMan

Reputation: 180

Using your layout

Using a helper column in column E

=C4+D4

Then drag that down

Putting this formula in G4 and dragging down should do what you want

=COUNTIFS(E:E;$G$3;B:B;F4)

and this in H4

=COUNTIFS(E:E;$H$3;B:B;F4)

As someone else pointed out, your sample expected result is incorrect if I understand what it is that you want to achieve correctly. You can change the Value in G3 and H3 respectively to adjust your criteria if necessary. In my test i had >=2 and >=3 respectively

Upvotes: 0

Related Questions