SUMIF & COUNTIF functions in Excel

[dwynnehugh]

Hi – any Excel experts here please – or anyone with any greater knowledge than I have!!  ??  That won’t be difficult.

I currently use an Excel spreadsheet to keep trace of my business matters – that works OK.  The main Excel sheet is named “Worksheet” and I know that if I wish to use certain details from the ‘worksheet’ to be inserted in another sheet in the same workbook I can do the following:

Assuming I wish to enter the contents of cell E43 in the ‘Worksheet’ into another sheet in the same workbook I can simply use  ‘=Worksheet!E43’ without the ‘ ‘s.

If I now wish to use the COUNTIF or SUMIF functions to record certain details from the ‘Worksheet’ into another sheet in the same workbook do I:-

use =Worksheet!SUMIF (range) or =Workbook!COUNTIF (range) or what?

Currently I have the SUMIF & COUNTIF functions located under the main spreadsheet on the “Worksheet”  HOWEVER when I set it to list certain sums/names in say rows (N6:N195)  it will do it  BUT despite having set up each one as shown I suddenly find that the criteria changes to (N3:N182) and several other options – this is even more predominant when I close and then re-open the Workbook sometime later.  This is especially aggravating as I know I set them all up correctly at the start. Anyone know what the heck I’m doing wrong or what is causing this please?

This is why I am asking if they would be better on a separate sheet in the same workbook – this would be my preference.

Thanks in advance,  Dave

The more you meet people the more you understand why Noah took animals instead of humans

[Robin Long]

The easiest way I would think of getting round your problem is probably to used named cells and named ranges and having the relevant sumif and countif function on the relevant sheet.  Without seeing the worksheet it would be difficult for me to see what is actually happening.

Cheers Knight,

RIP Spike09 Your Missed
If I'm not here, I'm there.

Finally joined Twitter! longr79

[dwynnehugh]

Thanks Robin, I will get back to you on this.  Dave

The more you meet people the more you understand why Noah took animals instead of humans

[blacklion1725]

Yes named ranges will help – the COUNTIF and SUMIF go first (=SUMIF at the begining). If you click on the little fx sign just left of the formula bar type COUNTIF and click on it. You can then build the function “live” by clicking on the sheet and cells you want to use, and you’ll see the correct function structure and result at the end.

I’d also recommend having a look at DSUM and DCOUNT which allow you to use more flexibility with the “if” criteria.

[dwynnehugh]

Thanks Blacklion

The more you meet people the more you understand why Noah took animals instead of humans

[Author]

Posts