SUMIFS problem

[PLacroix]

I'm attempting to create a formula to sum E:E based on text strings contained in A:A and B:B.

Here is what I tried:

=SUMIFS(D:D,A:A, "100",B:B, "Cold Call") (this is going into cell E to test it, but will be going on a different page later)

Right now its returning "0" when it should be returning a "2".

The data looks like this:

Column A	Column B	Column C	Column D	Column E
04/24/13/100	<p>Cold Call</p>	5:00:00 PM	1	Testing formula here "0"
04/24/13/107	Proposal Delivery	5:01:19 PM	1
04/23/13/100	Cold Call	3:34:12 PM	1

<tbody>
</tbody>

 

[Jonmo1]

Welcome to the board.

Having a hard time visualizing your data layout.

Try using the Excel Jeanie to post a sample sheet (see my signature for a link to the Jeanie)
Or use a Table in the forum Advanced Reply editor.


But at first glance, perhaps you need to remvoe the quotes from around 100.

 

[PLacroix]

I'll attach it asap, but removing the quotes didn't help.

The data sometimes has other letters in the cells with the text strings I'm searching for, so I'm wondering if this could be why its not matching them properly.

 

[andiemac]

Does column A contain the string "04/24/13/100"? If you are trying to match 100 you many need to break a down into 2 columns or use a differnt formula that will find 100 in the string first.

 

[jai9]

Hi,

Try

=SUMIFS(D1:D3,A1:A3,"*100",B1:B3,"Cold Call")

Jai

 

[PLacroix]

Does column A contain the string "04/24/13/100"? If you are trying to match 100 you many need to break a down into 2 columns or use a differnt formula that will find 100 in the string first.

Yes it does contain that string, and the other column I'm searching in sometimes has other text in it depending on how well our employees are spelling when they are entering data on their end. =(

 

[PLacroix]

Note Title	Note Body	Note Date	Value
04/24/13/100	Cold Call</p.>	4/24/2013 5:00:00	1	0

<tbody>
</tbody>

So I have hundreds of rows like this. I need to be able to sum the value of each employee by using the employee IDs in Column A (the three digit numbers) and the call types in Column B (eg: Cold Call) as the criteria.

I couldn't figure out Excel Jenie :s

PS: I really appreciate the help, and I can't get over how quickly so many people responded. This site is amazing! /favorited

 

[Jonmo1]

Looks lie jai9 has the answer in post #5

 

[PLacroix]

I tweaked it a little and it works! Thanks so much everyone!