help needed with COUNTIF and lookups (i think)

[NewbieMike]

Hi,

Iv'e been going round and round in circles with this for a few days and would be grateful if anyone can help get me on track.

I need to calculate the total license count by month for a number of users, each user can have different licenses assigned to them. I have created a sample spreadsheet with data and some sample values completed but i need to now calculate values automatically.

Thanks in advance.
Mike

 

[Nebu]

Hi:

May be something like this.

Thanks

 

[NewbieMike]

Hi Nebu,

Many thanks, i was no where near getting that right!

Thanks you, glad i found this place

 

[NewbieMike]

Just following on from this,

I have a flaw in my approach and would appreciate any suggestions

currently the lookup on the total license cost by month looks like this
=INDEX($B$28:$B$30,MATCH($A$9:$A$11,$A$28:$A$30,0))*B3

with the lookup for cost being flat, i.e.
License cost
SW1 23.50
SW2 17.10
SW3 15.00

However what the license model needs to look like is this
License users cost
SW1 2 23.50
SW1 10 18.00
SW2 100 17.10
SW3 100 15.00


So the total license cost by month should first SUM the total licenses needed for a month and license (i.e. Apr-15 for SW1) it should then look up the corresponding license cost base on total licenses required for month (users) and then return the correct cost * number of licenses.

IF SW1 and total licenses <= 2 total license cost for SW1=23.50*total licenses
IF SW1 > MAX(users) for SW1 total license cost = cost for max(users) * total licenses. i.e. SW1=18.00*total licenses

I hope that makes sense?

Thanks,
Mike

 

[NewbieMike]

i have found a flaw in the formula
=SUMPRODUCT((B$16:B$18=1)*($B$22:$M$24=$A3))

In user involvement by month if i change a user for a given month to a value > 1 then it breaks the formula and doesn't then show the correct figure. i.e. if Jan-15 USER B = 2 instead of 1

Then total licenses required by month should look be
Licenses Jan-15
SW1 2
SW2 0
SW3 2

Im continuing to try figure these out myself and will post update if i work out before someone has a chance to look.

Thank you.

 

[NewbieMike]

ok fixed this one..

Just following on from this,

I have a flaw in my approach and would appreciate any suggestions

currently the lookup on the total license cost by month looks like this
=INDEX($B$28:$B$30,MATCH($A$9:$A$11,$A$28:$A$30,0))*B3

with the lookup for cost being flat, i.e.
License cost
SW1 23.50
SW2 17.10
SW3 15.00

However what the license model needs to look like is this
License users cost
SW1 2 23.50
SW1 10 18.00
SW2 100 17.10
SW3 100 15.00


So the total license cost by month should first SUM the total licenses needed for a month and license (i.e. Apr-15 for SW1) it should then look up the corresponding license cost base on total licenses required for month (users) and then return the correct cost * number of licenses.

IF SW1 and total licenses <= 2 total license cost for SW1=23.50*total licenses
IF SW1 > MAX(users) for SW1 total license cost = cost for max(users) * total licenses. i.e. SW1=18.00*total licenses

I hope that makes sense?

Thanks,
Mike

 

[NewbieMike]

Thanks for all your help.. Was almost spot on but until id seen your way i was no where near getting it right!

i have found a flaw in the model that the formula doesn't work with.
=SUMPRODUCT((B$16:B$18=1)*($B$22:$M$24=$A3))

In user involvement by month if i change a user for a given month to a value > 1 then it breaks the formula and doesn't then show the correct figure. i.e. if Jan-15 USER B = 2 instead of 1

Then total licenses required by month should look be
Licenses Jan-15
SW1 2
SW2 0
SW3 2

Im continuing to try figure these out myself and will post update if i work out before someone has a chance to look. Ive made some changes on the pricing section to allow costs to be in bands so I've included a new spreadsheet.

Thank you.

Hi Nebu,

Many thanks, i was no where near getting that right!

Thanks you, glad i found this place

 

[Nebu]

Hi:

Please find the attached.

Thanks