Sumif based on the RESULT of a Countif

[Betsy Simpkins]

Hello Excel Gurus!

I'm struggling to get my SumIf to work because I want it to sum based on 2 criteria, one of which is that the result of the COuntif is >2. See below


A B
ex 1 5 3
ex 2 4 1
ex 3 10 6
ex 4 15 9

19 sum of A where the VALUE of B:B is <7, AND ONLY if the count of rows in B:B that are <7 is GREATER than 2



A B
ex 1 5 15
ex 2 4 1
ex 3 10 8
ex 4 15 9

0 The number of rows in B:B that have a value <7 is LESS than 2 so no sum.

I just can't figure out how to get the RESULT of the Countif used as the criteria in the Sumif.

THANKS for any help!

[kev_]

Please attach workbook free of sensitive data

To attach:
Click GoAdvanced \ look below and click ManageAttachments \ follow screen instructions
Thanks

[Root_]

Maybe

=(COUNTIF(B:B,"<7")>2)*SUMIF(B:B,"<7",A:A)

[Betsy Simpkins]

Thank you...attached a simple sheet up above with sample data.

[Betsy Simpkins]

Thanks...but wouldn't that result in the # of rows that are<7 *the sum of the ones <7? So in my example data above it would end up being 3*19...which is not what I need. I need only the 19.

[ben_hensel]

don't you just need to use an IF(COUNTIF, SUMIF, 0) ?

In B6:

Please Login or Register  to view this content.

I mean, doing it with Boolean arithmatic like Root suggested is almost equivalent, but this is clearer.

BTW @ Betsy
No, you're interpreting the formula wrong.
Its:
(COUNTIFS >2) * SUMIFS

(3 >2) = TRUE => 1
1 * SUMIFS

(2 > 2) = FALSE => 0
0 * SUMIFS

that's how it will be assessed.

[rajxl]

please try this one

[AliGW]

Rajxl - sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand.

[Betsy Simpkins]

Yes! This is perfect. Thank you so much Ben. I REALLY appreciate it. Also thanks for explaining the logic I missed, makes much more sense now. You guys are awesome!

[rajxl]

Thank you for your suggestion. I am feeling a mistake, next time I will take care of this matter. English is not my native language.

[ben_hensel]

@rajxl

Don't feel too down, we made it an unofficial rule because too many of the old hands were falling into that bad habit...!