how to calcuate data (simple divsion)BUT data changes w/ slicers

I'm not sure why sumifs would not work if it works in a pivot table. What do you mean by "it doesn't like using criteria thats horizontal and vertical"? You could upload an example to help if you can

for example, try do sumifs in B17. I tried...

=SUMIFS(B5:B13,A5:A12,"Admit",B4:I4,"October") but came up with #value

Using sumifs directly in the pivot table won't solve the issue, it will still change whenever you update it.

However, I am not entirely sure what is your problem here. Are you worried that the slicers could be updated which would change your results, which you don't want to? In that case you could use SUMIFS directly in the data that feeds your pivot table.

Otherwise, I don't understand the question

I apologize, let me try to clarify....

Example, the cells in the bottom table, I want all these to reference to the corrospoinding cells in the top table. (B18:B23 to corrospond to B5:B11). When no filters (slicers) are clicked, everything looks fine.

However, not everyone has data for october, novemeber and so forth. Example, perdiem do not have any admits for oct/nov. So if I click on "per diem", cell B5 (oct data) now turns in to Nov data and ruins my B18=B5/31.

Before I click slicer
B18=B5/31 gives me a value of 12.51

After I click slicer
B18=B5/31 gives me a value of 0 (should not be zero). Because October disappears, its as if it tied to only october. I want these cells to be dymanic.

Not sure if I am explaining myself correctly.

Hi,

That seems weird, or perhaps I don't understand. As I understand it, you choose Perdiem, and cell B4 becomes "December", right? In that case, the formulas below should not change if the formula really is "B5/31". Are you sure you are not getting formulas with GETPIVOTDATA?

yes originally I was getting "getpivotdata" but thats when i started typing in the actual cell reference. I solved my own problem, I had to increase the number of digits in the cell.

thank you