sum if statment

[porter2059]

is there a way to embed an "and statement" inside a sumif statement so that it sumifs based on the combination of 2 criteria? or is there a better way to do it?

 

[Eric W]

Welcome to the board.

Are you looking for the SUMIFS function?

=SUMIFS(sum_range,crit1_range,"criteria1",crit2_range,"criteria2")

There are more options, but if you need something specific, you'll need to provide sample data.

 

[porter2059]

Thanks Eric. it seems like the SUMIFS should work but it isn't. i can't figure out how to attach a file or a pic of the file here for you to see.

 

[Eric W]

You can use the HTML Maker in my signature. Or you can upload a picture of your workbook to a file sharing site such as Dropbox, then post a link to the picture. You can even upload your workbook to such a site and post a link, but many people will not open files from the internet for security reasons. Finally, when posting, you can click the "Go Advanced" button in the bottom right. That will open a larger toolbar, including a table tool. You can manually create a table showing your data and expected results. Somewhat tedious, but if your example isn't too large, it could work.

 

[porter2059]

Ok, I got the formula to work if I put my criteria1 and criteria2 in quotes. ie "assisted living - town" but I'm trying to use a cell reference for the criteria1 and criteria 2. should the cellular reference be a6, =a6, "a6", "=a6"?

 

[Eric W]

a6 is sufficient, but you can do "="&a6 if you want as well.

 

[porter2059]

[TABLE="width: 500"]
<tbody>[TR]
[TD]


[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]Amount
[/TD]
[TD][/TD]
[TD]town
[/TD]
[TD]Memo
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Net
[/TD]
[TD]Direct
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Town A
[/TD]
[TD]Direct
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Town A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Town B
[/TD]
[TD]Net
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Town B
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]Town A
[/TD]
[TD]Net
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]Town B
[/TD]
[TD]Direct
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD]Town A
[/TD]
[TD]Direct
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in cell j2 i put SUMIFS(B2:B6,D2:D6,"Town A", E2:E,"Net") then it will work but i want to use I2 instead of "Town A" or J1 instead of "Net" then it won't work

 

[porter2059]

Thank you. It is working as I had hoped for!

 

[Eric W]

Glad you sussed it out! As you probably figured out, by using the appropriate relative and absolute references in your formula, you can put the formula in J2 and then copy it to the rest of your table, without having to change it for each cell. I think this:

=SUMIFS($B$2:$B$6,$D$2:$D$6,$I2,$E$2:$E$6,J$2)

is probably what you came up with. Glad I could help!