Troubleshooting:How to set range automatically...in sumif function Error

How to set range automatically...in sumif function

How to set range automatically?
I am setting range manually and it is taking too much time,and tiredy work..i am fadeoff  it..is any simple formula to get sum accordance cell filled in A to B and sum inrespective column of G or H.
Example is following..

YOU CAN SEE AT A1 THIER IS TEXT( NAME), IN B1 TEXT , AND IN C1 ALSO TEXT ONLY 2( RURAL AND URBAN).
I WANT SUM OF "F1:F5" COLUMN, at G1, IF COLUMN " C" is rural , and IF COLUMN " C" is urban than sum at H1.(A2 to B5 are blank).
similarly
For SUM OF "F6:F9" COLUMN,at G6, IF COLUMN " C" is rural , and IF COLUMN " C" is urban at H6.(A7 to B9 are blank).
similarly
For SUM OF "F10:F18" COLUMN,at G10, IF COLUMN " C" is rural , and IF COLUMN " C" is urban at H10.(A11 to B18 are blank)
My problem is setting range i am applying at g1 =SUMIF(C1:C5,"rural"F1:F5) at H1=SUMIF(C1:C5,"urban"F1:F5).
and again at G6 and H6 i have to set range to C6:C9
similarly at G10 i have set range =SUMIF(C10:C18,"rural"F10:F18)
its to tired to set range, IS ANY EASY METHOD To set range automatically?.
               A               B             C                  D            E             F                          G                     H
1       TEXT        TEXT        RURAL      DATE1   DATE2    DIFF(F-D)            ANSWER REQUIRED
2                                         RURAL      DATE1    DATE2    DIFF(F-D
3                                         URBAN     DATE1    DATE2    DIFF(F-D)
4                                          RURAL     DATE1    DATE2   DIFF(F-D)
5                                        URBAN     DATE1    DATE2    DIFF(F-D)
6   TEXT   TEXT             RuRAL     DATE1     DATE2      DIFF(F-D)               ANSWER REQUIRED
7                                        RURAL     DATE1     DATE2     DIFF(F-D
8                                        URBAN    DATE1     DATE2    DIFF(F-D)
9                                          RURAL   DATE1    DATE2    DIFF(F-D)
10 TEXT        TEXT            RURAL     DATE1    DATE2     DIFF(F-D)                ANSWER REQUIRED
11                                        RURAL   DATE1       DATE2   DIFF(F-D
12                                      URBAN     DATE1     DATE2    DIFF(F-D)
13                                        RURAL    DATE1    DATE2    DIFF(F-D)
14                                        URBAN    DATE1    DATE2    DIFF(F-D)
15                                        RURAL    DATE1     DATE2    DIFF(F-D)                   
16                                         RURAL    DATE1    DATE2     DIFF(F-D
17                                      URBAN     DATE1    DATE2     DIFF(F-D)
18                                          RURAL    DATE1   DATE2    DIFF(F-D)

Keys to the Problem How to set range automatically...in sumif function

Download SmartPCFixer to Fix It (Free)

How to set range automatically?
I am setting range manually and it is taking too much time,and tiredy work..i am fadeoff  it..is any simple formula to get sum accordance cell filled in A to B and sum inrespective column of G or H.
Example is following..

YOU CAN SEE AT A1 THIER IS TEXT( NAME), IN B1 TEXT , AND IN C1 ALSO TEXT ONLY 2( RURAL AND URBAN).
I WANT SUM OF "F1:F5" COLUMN, at G1, IF COLUMN " C" is rural , and IF COLUMN " C" is urban than sum at H1.(A2 to B5 are blank).
similarly
For SUM OF "F6:F9" COLUMN,at G6, IF COLUMN " C" is rural , and IF COLUMN " C" is urban at H6.(A7 to B9 are blank).
similarly
For SUM OF "F10:F18" COLUMN,at G10, IF COLUMN " C" is rural , and IF COLUMN " C" is urban at H10.(A11 to B18 are blank)
My problem is setting range i am applying at g1 =SUMIF(C1:C5,"rural"F1:F5) at H1=SUMIF(C1:C5,"urban"F1:F5).
and again at G6 and H6 i have to set range to C6:C9
similarly at G10 i have set range =SUMIF(C10:C18,"rural"F10:F18)
its to tired to set range, IS ANY EASY METHOD To set range automatically?.
               A               B             C                  D            E             F                          G                     H
1       TEXT        TEXT        RURAL      DATE1   DATE2    DIFF(F-D)            ANSWER REQUIRED
2                                         RURAL      DATE1    DATE2    DIFF(F-D
3                                         URBAN     DATE1    DATE2    DIFF(F-D)
4                                          RURAL     DATE1    DATE2   DIFF(F-D)
5                                        URBAN     DATE1    DATE2    DIFF(F-D)
6   TEXT   TEXT             RuRAL     DATE1     DATE2      DIFF(F-D)               ANSWER REQUIRED
7                                        RURAL     DATE1     DATE2     DIFF(F-D
8                                        URBAN    DATE1     DATE2    DIFF(F-D)
9                                          RURAL   DATE1    DATE2    DIFF(F-D)
10 TEXT        TEXT            RURAL     DATE1    DATE2     DIFF(F-D)                ANSWER REQUIRED
11                                        RURAL   DATE1       DATE2   DIFF(F-D
12                                      URBAN     DATE1     DATE2    DIFF(F-D)
13                                        RURAL    DATE1    DATE2    DIFF(F-D)
14                                        URBAN    DATE1    DATE2    DIFF(F-D)
15                                        RURAL    DATE1     DATE2    DIFF(F-D)                   
16                                         RURAL    DATE1    DATE2     DIFF(F-D
17                                      URBAN     DATE1    DATE2     DIFF(F-D)
18                                          RURAL    DATE1   DATE2    DIFF(F-D)               


Try this formula in cell G1 and copy it down:

=IF(A1="","",SUMPRODUCT(OFFSET(F1,,,MIN(IF(A2:A$1000<>"",ROW(2:$1000)-ROW(A1),999))),
  --(OFFSET(C1,,,MIN(IF(A2:A$1000<>"",ROW(2:$1000)-ROW(A1),999)))="RURAL")))

Note; This is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

For cell H1, use the same formula, but with "RURAL" replaced with "URBAN".

Hope this helps / Lars-Åke

Machine Throttling

• Go to the Start button.
• In the Search box, type "regedit" (without quotes) and press Enter.
• Look for the following registry key: "HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\ SQMClient\Windows\DisabledSessions".
• On the right window pane, delete the subkey: "Machine Throttling".
• Restart your system.

Recommended Method to Repair the Problem: How to set range automatically...in sumif function:

 

 

How to Fix How to set range automatically...in sumif function with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your system.  Open it, and it will perform a scan for your computer. The junk files will be shown in the list.

2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been fixed.

Related: Startup Error Failed to connect to a Windows service System Event Notification Service sic Task Scheduler not always closing down program text wrap not working in Word Upgraded my computer from Windows Vista to Windows 7 Now my Lexmark Z515 printer will not work Can someone please help me The drivers for USB 2 0 arent showing up and dont seem to be usable I have ServicePack s 1 thru 3 The drivers used to show and work but not any more Is there a way I can get them back so my 2 0 items will work properly Symbols are not displayed in PDF when word is converted to pdf using Save As PDF Microsoft Addin The Security Center has not recorded an antivirus product when tried to generate System Health Report When conducting a search on Google or Bin search results are not visible I get a blank white page Ulead Photo Express 5 SE does not work with Win7 USB 2 0 devices plugged into USB 3 0 do NOT appear in Safely Remove HW VBA Excel find matching list files and copy to another folder What can I do to eliminate the not responding message I constantly get Web Proxy Not Responding USB Microsoft Mouse Not working upon boot whatsapp not connecting What should I do if my CD Drive reads some CDs but not others,Issues With Downloads And Installations Since Fresh Install Of Windows 7 64bit,Windows 8 - Browsers Font Rendering Issue! Blocky Fonts Text,Why Some Of Links Are Not Functioning In Web Pages (IE8)?,Internet Stops Working,Nesting Functions???,IMesh Is Not Working Only Minimizes,I Can't Uninstall ITunes And SupportSolutionsFramework.msi,I Have 3 Columns Out Of Many Columns Of Data Each With Hundreds Of Rows, I Need To Identify The Lowest Number In Each Row For These Three Columns Only And Reference The Column Title For That Particular Number.,Divide Information Into Two Columns,My Asus T100 Transformer Wont Turn On
Read More: Remove installed software in Windows 7 but ssd drive does not reflect space [Anwsered],[Solution] Outlook Express corrupt,Solution to Error: there is not enough free memory to run this program. exit one or more programs, and then try again,Computer won't start up after Windows updates,Solution to Error: Blue screen BCCode: 116,Alps pointing device stops working and resumes.,Attempting to use Conditional Formating with a variable,internet options not opening on IE 10,cannot open my emails. they are there but will not open,Microsoft Works 9 does not keep History items.