#### ExcelApprenticeChris

##### New Member

- Joined
- Mar 17, 2013

- Messages
- 9

Thank you for taking time to read my query/challenge?? I have been having a mental blank for the last two evenings on this and decided to ask you wise people for help.

The question summary is at the end. Sorry it is long, I have just tried to add as much detail as possible.

I have the following arrangement of data:

A | B | C | D | E | F | G | H | I | |

1 | Type 1 | Type 2 | Type 3 | Type 4 | Category | Categories: | |||

2 | 0 | 0 | 0 | 200000 | P | p | 1 | 3 | |

3 | 0 | 0 | 0 | 0 | Q | Q | 0 | 2 | |

4 | 26000 | 0 | 0 | 0 | R | R | 1 | 2 | |

5 | 400000 | 300000 | 40000 | 425000 | P | 1 | |||

6 | 0 | 75000 | 100000 | 175000 | P | 1 | |||

7 | 0 | 0 | 10000 | 350000 | Q | 1 | |||

8 | 120000 | 0 | 160000 | 50000 | R | 1 | |||

9 | 18000 | 0 | 12300 | 0 | Q | 1 | |||

10 | 0 | 0 | 0 | 0 | R | 0 |

<tbody>

</tbody>

I have been trying to build a formula in cell J2, for example, that says count how many times G2 appears in the range E2:E10 when the sum of each row is greater than 0. For example

**P**would return 3 because there are 3 instances where in the range E2:E10 P appears and each row sums to greater than 0.

The formula in J3 that is looking up G3 (i.e.

**Q**) on the other hand would return 2 and not 3 because although there are 3 instances of

**Q**appearing in the range E2:E10, only twice does the row in which it appears sums to greater than 0 because in row A3:D3 where the

**Q**in E3 appears, it sums to 0.

I have tried various SUMIFS, COUNTIFS and SUMPRODUCT and IF formulas exhausting my knowledge but frustratingly I could only count every time it appeared the categories in G appeared in the range on column E. So I always over counted because P returns as the desired 3 but Q and R for example returns as the 3 and 3 respectively and not the 2 and 2 expected.

I have found a temporary workaround using simple IF((SUM and COUNTIF by putting a formula in column H2:H4 of the following form: =IF((SUM(A2,B2,C2,D2))=0,0,1) which simply returns a 1 if the sum of the row for columns Type 1 to 4 is greater than 0 and it returns a 0 if equal to 0. So as expected cell H3 and H10 are zero and the rest are 1.

This is then coupled with the formula in I2:I4 of the form: =COUNTIFS(E2:E10,G2,H2:H10,"1") which counts the number of times for example G2 (i.e. P) appears in the range E2:E10 when its corresponding cell in the range H2:H10 is 1 i.e. it is adding up to more than 0 in the row of the type 1-4 columns.

**So my question**i

*s can anyone put this operation (these two formula) into a single formula that removes the need to have the columns H and I, so i can just have a single string in J2:J4.*

I have tried all sorts of SUMPRODUCT, SUMIFS and COUNTIFS combinations but at a mental block trying to get the correct solution.

Can anyone create a formula that counts the amount of times a category in column G appears in the range E2:E10 when its corresponding SUM of the rows - for the 'type' columns - add up to more than 0.

I have tried all sorts of SUMPRODUCT, SUMIFS and COUNTIFS combinations but at a mental block trying to get the correct solution.

Can anyone create a formula that counts the amount of times a category in column G appears in the range E2:E10 when its corresponding SUM of the rows - for the 'type' columns - add up to more than 0.

Hopefully it is not too much of a challenge!

Anyway if you made it this far, thank you very much for your help. I really appreciate your time. Thank you.

Christopher

PS if you need any more detail please do not hesitate to ask.