Help with sumproduct formula

[PaulPogba]

Hello everyone, this is my first post,

I have tried searching the forum but wasn't exactly sure what to search for and couldn't find an answer

I have managed to get this formula from searching on the internet, which is working

=SUMPRODUCT(SUBTOTAL(103,OFFSET(H2:H8,ROW(H2:H8)-MIN(ROW(H2:H8)),,1))*(H2:H8="p"))

however, instead of using p I would like to use a number, lets say 1 in this instance, so it would give me a total of all the 1's shown in H2:H8

can this be done or does it have to be text ?

thanks in advance for your help

kind regards

PG

 

[dreid1011]

Welcome to the board.

You can use any criteria that you want. Just don't put "" around the 1 if you are looking for a real number vs number stored as text.

 

[PaulPogba]

Thank you dreid1011, that works, so simple when you know how!

Also, I have tried to modify the range it is looking at, as I am constantly adding new rows. I have tried just putting H:H , instead of H2:H8, so it will count everything in column H but it doesn't work.

Do you know how I can do this?


Thanks again , PG

 

[dreid1011]

You could just point it to a range a bit larger than what you think you might use, otherwise I need to lookup again dynamic ranges. Or you could name the range, and use a dynamic formula:

http://www.excel-easy.com/examples/dynamic-named-range.html

 

[PaulPogba]

Thanks again dreid1011, the dynamic ranges seem a bit too complicated for me so I have just extended the range it's pointing to

Using the formula I posted above , is it possible to modify it to look at second column and return a count ? say if cell A1 = X and cell B2 = Y, then it would give a total 1

 

[dreid1011]

Yes, something like:

=SUMPRODUCT( ... *(A1=X)*(B2=Y))

 

[PaulPogba]

That worked a treat , thank you so much for your help