Multiple If statements in one formula performed in sequence

[adray13]

Hi,

So, ultimately I am providing demand over time for several different buildings while people are moving in and out them. There are 2 parts to the formula I need and I have the first part. I have people moving out in one pivot table and people moving in in another table. I have to find a way to use both of these pivot tables in a formula to get my demand over time which is in another tab.

My main tab "Supply Demand-All by Qtr" has an initial demand number then from there it will subtract the number of people in the "PT by Qtr" tab whenever the building name and time period is equivalent. Below is the first part of the formula I have that works. C4 is the initial demand number.
=C4-IFERROR(GETPIVOTDATA("p00_HC",'PT by Qtr'!$A$3,"Qtr Date","2Q 2013","From_bl_id","BELL"),0)

Now I need to do pretty much the same thing again except I need to add the number of people in the "Camping PT by Qtr" tab when the building name and time period are equal.
I tried just adding a +(IFERROR(GETPIVOTDATA("p00_HC",'Camping PT by Qtr'!$A$1,"Qtr Date","3Q 2013","BL","BELL"),0)) to the end of the formula, but it didn't work.

I have attached a spreadsheet with some sample data for review.

Any help would be much appreciated!

[djapigo]

Hi adray13,

Try this formula in D4... =C4-IFERROR(GETPIVOTDATA("p00_HC",'PT by Qtr'!$A$3,"Qtr Date",D$3,"From_bl_id",$B4),0)

Then copy this to the rest of the cells...

Let me know if this is what you want...

Thanks,
Dennis

[adray13]

I have not tried it yet, but had a quick question first. It looks like the formula you suggested is looking at the pivot table "PT by Qtr" which isn't the part I need. I need to add a value from the pivot table "Camping PT by Qtr" after subtracting from the "PT by Qtr". There is two parts. Am I misunderstanding your suggestion?

[djapigo]

My mistake... it would help if I actually read your original post more closely...

Try this...

=C4-IFERROR(GETPIVOTDATA("p00_HC",'PT by Qtr'!$A$3,"Qtr Date",D$3,"From_bl_id",$B4),0)+IFERROR(GETPIVOTDATA("p00_HC",'Camping PT by Qtr'!$A$1,"Qtr Date",D$3,"BL",$B4),0)