sumifs assistance please

[J G]

I need a formula which says sum the amount column on the all sheet when 2 criteria are met:
The first criteria is sum for the correct time period. To accomplish this I created a period code. To ensure unique values in the period code I multiplied the period number by the year.
The second criteria is sum revenue and expenses separately. Revenue accounts are all less than or equal to 49999. Expenses are greater than or equal to 50000.

I've attached an example

 

[Chihiro]

Something like attached?

FYI - Main issue here was that Account column was in text format. It's been converted to numbers.

 

[J G]

I cant get my file to work like yours. did you only change the format type of column A to get this to work? I've change mine to match your but it still only returns zeros. I saw you added the abs to the formula but that shouldn't make it work.

thanks for the assist.

 

[Chihiro]

There are few ways to change format.

1. Select from A2:A502 then scroll back to A2 without changing selection. Click on the "!" icon and change to number format.

2. Select Column A. Go to Data tab in Ribbon tool. Click on "Text to Columns", hit next leaving everything as default and finish.

3. Multiply text value with 1, or add or subtract 0.

FYI - ABS() just makes the formula result absolute value (i.e. -100 becomes 100, 100 remains as 100).

 

[J G]

Thanks very much, that works like a champ!

I cant tell you how many times I get caught up with formatting issues. I expect to use this text to columns fix in the future.

Thanks!