SUMIFS problem

PLacroix

New Member
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

Cold Call

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>

Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Jonmo1

MrExcel MVP
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)

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

PLacroix

New Member
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

Board Regular
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

Active Member

Hi,

Try

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

Jai

PLacroix

New Member
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

New Member

 Note Title Note Body Note Date Value 04/24/13/100 Cold Call 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

MrExcel MVP
Looks lie jai9 has the answer in post #5

PLacroix

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,682
Messages
5,838,789
Members
430,569
Latest member
sbardelli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back