Formula for counting multiple variables in a spreadsheet

S

smorgan

I have a spreadsheet with multiple columns. I am trying to create a formula
that would return a count for the number of values in column A that has the
same value I am looking for in column E.

The formula that I thought would be if(e4:e1000="FL1",countif(A4:a100,a4),0)

Any ideas....
 
S

SteveG

SUMPRODUCT should work. Try this,

=SUMPRODUCT((A4:A1000=A4)*(E4:E1000="FL1"))

The ranges need to be the same size.

Cheers,

Steve
 
W

WCoaster

The formula would be:
=COUNTIF(A4:A1000,E1)

This would count all the incidence of E1 in A1:A1000. Likewise you could
copy this formula beside every unique value in Column E.
 
S

smorgan

Thanks for the response. I may have not been real clear. I am looking to
count all of instances of say "John Deere" in Column A that have the value of
"FL" in Column E.
The formula would be:
=COUNTIF(A4:A1000,E1)

This would count all the incidence of E1 in A1:A1000. Likewise you could
copy this formula beside every unique value in Column E.
I have a spreadsheet with multiple columns. I am trying to create a formula
that would return a count for the number of values in column A that has the
[quoted text clipped - 3 lines]
Any ideas....
 
S

SteveG

The SUMPRODUCT will work.

=SUMPRODUCT((A4:A1000="John Deere")*(E4:E1000="FL"))

This counts the number of times that a cell in column A = John Deere
where the corresponding cell in column E contains "FL".

Steve
 
W

WCoaster

Extremely creative solution. Thanks, I am sure I will be able to use that
somewhere soon.
 
S

sudhi

Dear Sir/Madam

I have just started MS Excel and VBA so If u have got any source
material plz send this mail id


yours

sudhir
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top