applying countif and sumif formula on multiple tables

**Anzar** · 01-19-2008, 10:45 AM

on sheet, "voice call analysis & search", there are certain tables,

some formulas are used upon them...they extract things from sheet"itemized call details", in that sheet thre are more than one table....and i cannt use those formulas from multiple tables...

**oldchippy** · 01-19-2008, 01:20 PM

Hi,

You need to separate the different tables, then use SUMPRODUCT to sort out the costs and times associated with each number.

NOTE: Check the ranges in the formulas so that you are happy all the data in each table will be counted

**Bjornar** · 01-20-2008, 10:33 AM

Hi Anzar,

I have done 2 changes to your sheet "Itemized Call Details".
In table number 3, I have inserted a new blank colum so the "Call Number", "Duration" and "Charges" columns of all 3 tables is in the same column.

If you have tried to select a single column from all 3 tables you would have noticed that all columns get selected. This is because you have merged the cells for the headers. I have fixed this too. I have removed the merging, then selected the cells that were merged and in the menu Format->Cell->Alignment->Horisontal i have selected "Center Across Selection". This gives you the same visual look as merging cells, without the cells actually beeing merged. Now you can select a single column from all 3 tables and use in your formulas.

Merged cells will often give you trouble with lookup formulas, so "Center across selection" is almost always a better solution to use.

Hope this helps.