• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF formula using 3 criteria

ExcelNoob_111

New Member
Hello All,

I am attempting to write a formula using multiple criteria.

I would like to get a result that comes from the formula looking at 3 different columns of cells in a different tab.

Tab 1 ("Summary") has the summary table. In this table I would like to show a breakdown of a larger set of raw data in Tab 2 where the columns are located. The formula will go into each cell of the table in this tab.

Tab 2 (call it "Floor 1")
Look up column 1 (call it "Space) for the text status "Y"
Look up column 2 (call it "Room") for the text "A", "B", or "C"
Look up column 3 (call it "UIT") for the values that share the same row as column 1 and column 2 to get the result in Tab 1.

Example:
I want to see the total results of the specific UIT values from "Y" Space in "A" Room

So basically, I want to sort through columns 1 and 2, and show the TOTAL visible result based on the values in column 3.

I am currently using a SUMIF formula, but I have too many criteria, and have run into a wall as to which formula to use.

Please see attached for an example.

Any and all help will be greatly appreciated.
 

Attachments

  • Space Example.xlsx
    9.3 KB · Views: 3
Hi:

Your requirement is not super clear , can't you use a pivot for this? If you are looking to summarise data by Room& Space. If this is not what you are looking for, could you manually show what is your expected results

Thanks
 
I've attached a revised file with the desired result manually completed.


I could do a pivot, but I want the table in the Summary tab to automatically update based on the results of the later tabs.
 

Attachments

  • Space Example.xlsx
    9.6 KB · Views: 7
Hi:

How many tabs of data you have? Do all the data sit in the same format?, The best way to do this consolidate all the tabs into one master tab and pull a pivot out of this.

Thanks
 
9 tabs for "Floor", so there are 9 floors, but it is preferred to keep them separate.

I wish for a formula to place into the cells of the Summary table to look at each "Floor" tab and come up with a result into each cell.
 
Hi:

Find the attached. I have used volatile function Indirect to achieve this, won't recommend it unless you have no other choice, but will give you what you are looking for. I have done it for 3 sample floors.

Note:
I am assuming the data structure will be same across all the tabs.

Thanks
 

Attachments

  • Space Example.xlsx
    12.8 KB · Views: 6
The main difference between this solution and that of @Nebu is that I have used CHOOSE rather than INDIRECT to switch the formula between sheets. There are presentational differences because I never use direct cell references but that is a separate matter. The final formula reads

= SUMIFS( UIT, Room, Grade, Space, "Y")

but that clearly hides complexity behind a disarmingly simple formula. The key element of the formula is the definition of references 'Room', 'Space' and 'UIT'.

= CHOOSE( Floor, Floor1[Room], Floor2[Room], Floor3[Room] )
= CHOOSE( Floor, Floor1[Space], Floor2[Space], Floor3[Space] )
= CHOOSE( Floor, Floor1[UIT], Floor2[UIT], Floor3[UIT] )


respectively. 'Floor' is a (mixed) structured reference that refers to
= Summary[@Floor]
and 'Grade' refers to the heading row of the Summary table
= Summary[#Headers]
and is picked up by implicit intersection since multi-cell array formulas are not allowed in tables.
 

Attachments

  • Space Example (PB).xlsx
    17.6 KB · Views: 8
@ExcelNoob_111
From the name you have adopted, my workbooks might be somewhat tough going. My apologies for that.

@Ninjas
I have tried some alternative strategies. This one involves only one named formula that runs across multiple worksheets.

'Availability':
= CHOOSE( Floor, Floor1, Floor2, Floor3 )
reduces the effort that has to be invested in writing CHOOSE formulas and ensuring consistency.

The single column references that are used in the SUMIFS then become a set of INDEX lookups for 'Room', 'Space' and 'UIT'
= INDEX( Availability, , 1 )
= INDEX( Availability, , 2 )
= INDEX( Availability, , 3 )

This does rely upon the columns appearing in the same order but I do not think this would be a great handicap.

The final calculation is still
= SUMIFS( UIT, Room, Grade, Space, "Y")

Which approach do you regard as preferable (of course, you have a perfect right to hate both!)
 

Attachments

  • Availability (PB).xlsx
    16.2 KB · Views: 11
Back
Top