Why doesn't this formula work?

[rboyd_01]

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.
Please help me I've been trying to get this to work for 5 hours.

 

[Brian from Maui]

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.
Please help me I've been trying to get this to work for 5 hours.

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]

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]

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!