SUMIF Formula Question-

[shipp1ab]

I am using the SUMIF formula to pull the sum of each location by month from corresponding sheets, See the photo below for my comparison sheet I am creating.

We will use the first location "Ann Arbor Education Center" as an example. You will see the current formula I am using above, this is the one I am using because some locations have multiple listings each month, so I want the sum of those. Now, see the photo below, this is my March sheet, the comparison sheet above is pulling 1 which is correct.

Now, see the photo below, this is my Feb sheet, the comparison sheet above is pulling "0" which is correct.

Now my question, is there a way to pull either a blank or N/A if there is no data to be found. See photo below for my April sheet. You will see in the comparison sheet above it is putting a "0" for April, but I don't want anything listed if I have no information for that month.

My preference would be for it to look like one of them below.

I am usually pretty good at finding the formulas I need but I am struggling with this one.

Thank you!

 

[DanteAmor]

Hi and wlecome to MrExcel!

Try this:

=IF(MATCH($A3,'Apr 2023'!$B:$B,0),SUMIF('Apr 2023'!$B:$B,$A3,'Apr 2023'!$D:$D))

NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


Example:

Dante Amor
	A	B	C	D	E
1	Segment
2	Location	Jan	Feb	Mar	Apr
3	Ann Arbor				#N/A
4	Berrien				23
Sum

Cell Formulas
Range	Formula
E3:E4	E3	=IF(MATCH($A3,'Apr 2023'!$B:$B,0),SUMIF('Apr 2023'!$B:$B,$A3,'Apr 2023'!$D:$D))

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=IFERROR(1/(1/SUMIF('Apr 2023'!$B:$B,$A3,'Apr 2023'!$D:$D)),"")