COUNTIF or SUMIF Question

[William Rivero]

Hi, I've been stuck with this problem in excel. I have 6 columns:

___A____B___C___DD___EE___FF
1__Y_____________Y
2_______Y
3_______Y__________________Y
4___________Y_________Y
5__________________________Y

Problem: Count if any of A1:C5 has a "Y" AND DD1:FF5 has a "Y" too. In this example the formula should return a value of 3.

Thank you.

 

[taurean]

Welcome to the board.
In a blank column insert this formula:
=IF(ISERROR(AND(FIND("Y",CONCATENATE(A1,B1,C1)),FIND("Y",CONCATENATE(DD1,EE1,FF1)))),"","YES")
Copy it down to the last row.
At the bottom to get the total use this countif:
=COUNTIF(D1:D5,"YES")
The above formula I have written for column D.

There are array formulas, which I believe will give the answer in one go. They are elegant but I have not learned them yet.

 

[AlphaFrog]

Hi and welcome.

This works if the other cells are empty.
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A1:A5&B1:B5&C1:C5="Y"</font>),--(<font color="Red">D1:D5&E1:E5&F1:F5="Y"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

This works whether the other cells are empty or used.
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Y</td><td style="text-align: right;;"></td><td style=";">n</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">n</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">ISNUMBER(<font color="Green">SEARCH(<font color="Purple">"Y",A1:A5&B1:B5&C1:C5</font>)</font>)</font>),--ISNUMBER(<font color="Red">SEARCH(<font color="Green">"Y",D1:D5&E1:E5&F1:F5</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[William Rivero]

This is great, both solutions work! Thanks for your help!

 

[William Rivero]

Looks like I have another problem that is linked to the one I posted above.

Formula: =SUMPRODUCT(--(G5:G1048576&'H5:H1048576&I5:I1048576="Y"),--(J5:J1048576&K5:K1048576&L5:L1048576&M5:M1048576=""),--(U5:U1048576>4))

Returns a #DIV/0 error for some reason.

The last expression "U5:U1048576>4" is the result of S5:S1048576 divided by T5:T1048576.

What am I doing wrong?

 

[AlphaFrog]

Somewhere in column U there probably is a #DIV/0 error. And that error is probably because there is a zero or blank value somewhere in column T.

TIP: It would be much more efficient to use the approximate number of filled cells in the Sumproduct formula; e.g. G5:G10000 instead of using the max rows on a spreadsheet 1,048,576

 

[William Rivero]

Brilliant! Thanks again!

 

[William Rivero]

|-----ARF----| |-----------CRF------------|
A B C D E F G H
1 Y Y Y Y
2 Y Y Y
3 Y Y
4 Y Y Y Y
5 Y

Problem: I want to count rows in columns A:C with more than 1 "Y" (should return a value of 2 in this case) and do the same with columns D:H (should return a value of 3).

Problem2: Also I want to count rows with >1 "Y" in A:C AND D:H (should get 2 [for rows 1 and 4])

Thank you in advance. I know I'm missing something somewhere. Tried a very very long equation but its inaccurate.

 

[William Rivero]

___A____B___C___DD___EE___FF
1__Y________Y_________Y____Y
2_______Y
3_______Y_____________Y____Y
4_______Y___Y_____Y____Y
5__________________________Y

Problem: I want to count rows in columns A:C with more than 1 "Y" (should return a value of 2 in this case) and do the same with columns D:H (should return a value of 3).

Problem2: Also I want to count rows with >1 "Y" in A:C AND D:H (should get 2 [for rows 1 and 4])

Thank you in advance. I know I'm missing something somewhere. Tried a very very long equation but its inaccurate.