# Boards

## Excel help!

This is not going to be as fun as the D v _ J thread but i need help with an excel problem.

Basically I need to be able to add up numbers in a row every time the word 'invoiced' appears.

You would think this would be easy. It isn't. I have been staring at excel for so long, it is making it worse!

Does anyone have any idea how to do this?

## SUMIF

## OR SUTIN!

## Sumif

## Easy

Just press 'F1'.

## out of interest what form would the data take

would it be like A2:1 B2:2 C3:invoice, with C3 = A2 + B2

## C2*

oh and yeah SUMIF

## an if statement should do it..

=IF(A1="invoiced",SUM(G1:K1),0)

column A would have invoiced in it, G1:K1 is the range of your row,

## See I typed in this formula and it came up with an error message saying it was invalid

Though my 'column A' is a row. If that makes sense?

## would this work?

=IF(A1="invoiced",SUM(A2:A10),0)

## :( it's now saying it = to £0,00

Right this is what it looks like:

Worksheet 1 = Summary Page

Worksheet 2 = detailed Page

Block on worksheet 1: TOTAL INVOICED

Worksheet 2: from row D28 to AF28 there are a few boxes filled in with INVOICED

I want to add up all of the blocks above where the word 'INVOICED' appears and show the total in the 'TOTAL INVOICED' block on the Summary page.

Does this make any sense?

## =COUNTIF(D28:AF28 ,"invoiced")

## Sorry, should have explained that the blocks above the word 'INVOICED" is filled with a total amount invoiced

not how many times the word invoice appears. So I want to know how much money has already been invoiced.

## =SUMIF(D28:AF28,"invoiced",D27:AF27)

(if the amounts are in row 27)

## if you're doing it on sheet 3..

=SUMIF(Sheet2!D28:AF28,"invoiced",Sheet2!D27:AF27)

## although it'd be

=countif(Sheet2!D28:AF28,"invoiced")

put that ^ in the cell where you want to show the 'total invoiced'.

## =sumif(D28:AF28,"INVOICED",D27:AF27)

## use the data

you plotted the graph with, use a formula based on a value you enter into a named cell to follow the rule the data projected.

## that would depend on what the data predicted...

Id assumed that you had some data to create the table from, then with the help of the graph you'd be able to project/predict. you'd use algebra so a straight diagonal line could be, if X=1 Y=1, if X=5 Y=5, (<-very basic) youd then create a formula in excel to follow that rule... and use a named range as a variable

## in the projected trendline options, tick show formula

it'll be in the form y=...

use that formula to calculate y for any given value of x

## Alt + F4 should do the trick

## CTRL + ALT + down arrow when highlighting the word

## Copy all the data into a SQL database.

Then just use

Select Count(*) where {text_field} like '%invoiced%'

There's a reason Excel is pretty much always the worst tool for the job; often it combines this with being the only tool for the job too. Utter shitcunt of a program.

## What's an 'SQL' database????

I'm so confused!

## Well MS Access sort of tries this.

It's just a relational database. It's what DiS uses, it's what the vast majority of databases you'll ever interact with use.

Actually that SQL is missing a FROM statement but you get the idea. This is what databases are for.

## ^ least helpful post in the history of DiS.

## Well the answer had already been given by thewarn.

I assumed it was all taken care of and all we had left was to grumble about how horrendously awful any time spent using Excel is.

I'M SORRY.

## I'm fully appreciative of your effort to help me.

honestly I am. THis is all just too stressful as I would have thought it would be easy. I have been trying 'sumif' but it keeps saying an 'invalid formula' message. :''''(

## I dunno.

I thought John and Thewarn were sorting you out.

I hate Excel. I only use it to do stage 1 of bulk find and replace on rows of data. Stage 2 involves using Notepad++ to find and replace all the crap that Excel forced on me. :D

## :D

You plum.

## Im sure she got the idea though

## ignore him

## ^^^ Ignore him.

Excel lover.

## I got called an 'excel wizz' the other day

for teaching someone how to add a hyperlink to a cell.

## ^this

I get treated like a guru because i know how to change the colour of a cell

## Can you do that?

## th above formula should work..

go to your summary page and select the cell where you want the result to show.

in the formula bar type in =sumif( then use the mouse to select sheet 2, highlight the row 28, columns D to AF press comma, then type "Invoiced" press comma then select all the data in the rows above invoiced, columns D to AF and press return...

## :D It WORKED!!!!

Thank you so much everyone!

-------------END OF THREAD--------------------

## No

Because you have your data going across a row, pretty sure you can only filter columns.

## Well you better explain to her to copy + paste special, tick transpose

Probably best to paste it on a new sheet

## What Matt_was_taken explains really should work

Alternatively type:

=sum(if(Sheet2!d28:af28="Invoiced",Sheet2!d27:af27,0)

and press ctrl+shift+enter

Your formula should appear in {curly brackets}

## Yep. It all worked!

:D

## I'm so glad I work at a job where I only use Excel to book in holiday time.

NEEEEEEEEEEERRRRRRRRRRRRRDDDDDDDDDDDDSSSSSSSSSSSSS.