Why doesn't this formula work?

rboyd_01

New Member
Joined
Dec 7, 2005
Messages
17
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.
 

Some videos you may like

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
Joined
Feb 16, 2002
Messages
8,458
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.
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
 

rboyd_01

New Member
Joined
Dec 7, 2005
Messages
17
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top