Creating A Total Based On Multiple Conditions

[MOOK]

I'm working on a spreadsheet and my head has nearly exploded fro
frustration.
The problem is, i need the spreadsheet to count cells in one column bu
only if they hold certain data from another cell.

I found one sample formula which is:

=sum(if((B5:B25="NORTHWIND")*(C5:C25="WESTERN"),F5:F25))

but it doesn't seem to work, i'm quite new to excel so maybe i jus
don't understand it, so explaining what it means would be good.

Erm...... does that make sense????

Can anyone help before i go totally insane? :confused

 

[Andy B]

Hi

You may need to enter that formula with Ctrl Shift and Enter - as it is an
array formula.
An alternative is to use SUMPRODUCT:
=SUMPRODUCT((B5:B25="NORTHWIND")*(C5:C25="WESTERN")*(F5:F25))

Hope this helps.

 

[Eamon]

Try using =COUNTIF(A1:A30,7)

This reads as look in the range of cells A1 to A30 and count the cell
that contain the value 7

 

[MOOK]

UNFORTUNATLY I'VE ALREADY TRIED THAT FORMULA AND IT DOESN'T WORK.
UNFORTUNATLEY FOR ME MY QUERY INVOLVES MULTIPLE ARGUMENTS AND THA
FORMULA IS ONLY GOOD FOR SINGLE ARGUMENTS.

I'VE TRIED TO CLARIFY EXACTLY WHAT I;M TRYING TO DO BELOW.

IF COLUMN A1 HAS THE NUMBER '29' IN IT THEN I WANT THE SPREADSHEET T
COUNT IT BUT ONLY IF THERE IS A '1' IN COLUMN B1
:confused

 

[Jim Wood]

I had a similar problem trying to count the number of times a code letter/number appeared in a column based on criteria in other columns until I found that if I treated the numbers as such, instead of as text as they were formated to 'look' like on the spreadhseet, the function would count them correctly. Try something like this

=sum((ColumnName1="Text You're Looking For")*(ColumnName2=Numeric Your Looking For)

Enter this function as an array (control+shift+enter)

----- MOOK > wrote: ----

I'm working on a spreadsheet and my head has nearly exploded fro
frustration
The problem is, i need the spreadsheet to count cells in one column bu
only if they hold certain data from another cell.

I found one sample formula which is

=sum(if((B5:B25="NORTHWIND")*(C5:C25="WESTERN"),F5:F25)

but it doesn't seem to work, i'm quite new to excel so maybe i jus
don't understand it, so explaining what it means would be good

Erm...... does that make sense????

Can anyone help before i go totally insane? :confused