[NEED HELP]Array Formula by Referring to Few Criteria in Different Table

Dear All,

I need some help in putting the array Formula Referring to Few Criteria in Between 2 Tables in different sheet.
Table 1 in "Raw Data" sheet while Table 2 in "Bill" sheet.

Table 1 table2_2.png

Table 2 table1_2.png

In Table 1, Column "D", the formula is "=IF([@[ActualBilling (Month)]]>0,1,0)"

In Table 2, Column "D", the formula is "=SUMIFS('Raw Data'!$E$5:$E$9028,'Raw Data'!$D$5:$D$9028,Bill!B57,'Raw Data'!$A$5:$A$9028,Bill!$E$4)"

At Column "E57", currently my formula is "{=MAX(IF(AND($D57=0,Billing__Stage=$E$4,Creteria=$B57),'Raw Data'!$B5,'Raw Data'!$C5:$C12))}"

and at column "E57" there are some conditional formatting where to change the cell colour to Green, Blue, or White when cell Column E=8, E<8, E=0.


My issues is current array formula i used i need to manually identify the range of cell in Table 1, ActualBilling column for unit A-3-A to A-3-H, is there any other method which allow me to select the overall range in ActualBilling column, they will select the relevant range and calculate it. And i can copy and paste this formula to other cell.

Thank you.

Hi,

Can be solved by changing the formula to this:

Formula:

Please Login or Register  to view this content.

This changed formula will create a range by using the condition billing_stage and tower. Because you've used merge field for the billing stage, make sure you enter the correct cell for billing stage and tower.

Originally Posted by joris moerings

Hi,

Can be solved by changing the formula to this:

Formula:

Please Login or Register  to view this content.

This changed formula will create a range by using the condition billing_stage and tower. Because you've used merge field for the billing stage, make sure you enter the correct cell for billing stage and tower.

Trying this in cell E57, and get the answer, however, when apply this to other cell, E56, E55, E54 and so on, it show the same answer as E57. Any solution?

$C$1 is the "Anchor" (starting point) of the offset function. Everything is relative to this cell - Start rows for example.

You cannot simple copy the formulas: you need to change the Criteria cells e.g $E$4 is "2a" so need to change to cell which contains "2b" etc.

Also as I said in my earlier post, I couldn't determine Tower A data from Tower B.

Changing the criteria also applies to posting by Joris

The formula is still an array formula that part hasn't changed. So it still needs to be eneterd with CTRL+Shift+Enter