# Why doesn't this formula work?

#### rboyd_01

##### New Member
I have created a formula, and altered it many different ways. Every time, it won't give me the correct answer.
The sheet that it is pulling from has the person's name, and what the issue was. So I want to make a formula that will take the whole sheet with everyone's name and their issue, and count the number of times each issue comes up for each person.
Here are a couple of the formula's I've used that aren't working.
Just so you know Main D:D is the column with each person's name in it
D3 in this sheet is the person's name I'm looking to pull info for
Main G:G is the issue
I2 in this sheet is the issue I'm trying to pull for
{=SUM(IF(Main'!\$D:\$D=\$D3,IF(Main'!\$G:\$G=\$I\$2,1,0)))}
{=COUNT(IF(Main'!\$D:\$D=\$D3,IF(Main'!\$G:\$G=\$I\$2,1,0)))}
and I've done these without being an array.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Brian from Maui

##### MrExcel MVP
rboyd_01 said:
I have created a formula, and altered it many different ways. Every time, it won't give me the correct answer.
The sheet that it is pulling from has the person's name, and what the issue was. So I want to make a formula that will take the whole sheet with everyone's name and their issue, and count the number of times each issue comes up for each person.
Here are a couple of the formula's I've used that aren't working.
Just so you know Main D:D is the column with each person's name in it
D3 in this sheet is the person's name I'm looking to pull info for
Main G:G is the issue
I2 in this sheet is the issue I'm trying to pull for
{=SUM(IF(Main'!\$D:\$D=\$D3,IF(Main'!\$G:\$G=\$I\$2,1,0)))}
{=COUNT(IF(Main'!\$D:\$D=\$D3,IF(Main'!\$G:\$G=\$I\$2,1,0)))}
and I've done these without being an array.

Try, enter with just Enter

=SUMPRODUCT(--('Main'!D2:D100=\$D3),--('Main'!G2:G100=\$I\$2),RangeToSum)

=SUMPRODUCT(--('Main'!D2:D100=\$D3),--('Main'!G2:G100=\$I\$2))

Are you trying to count or Sum? The first formula is to sum the second to count.

No whole column references

Use a Pivot Table for a count of names and issues

#### colbymack

##### Active Member
Have you tried using a SUMPRODUCT formula yet? I think this will get you want you want - look for sumproduct solutions in this forum that also have "--" included in them:

Here is an example that you can model from:
http://www.mrexcel.com/board2/viewtopic.php?t=184333

#### rboyd_01

##### New Member
Thank you soooo much the formula
=SUMPRODUCT(--('Main'!D2:D100=\$D3),--('Main'!G2:G100=\$I\$2))
worked perfectly. You guys are the best. I was using excel help that's where I got the formula to use either count or sum. Excel's help isn't very good, that's why I love this site!

Replies
1
Views
96
Replies
15
Views
205
Replies
3
Views
358
Replies
17
Views
354
Replies
5
Views
69